none
SQL Matrix Query

    Question

  • Hi,

    I want to create a view or stored procedure that will display results in a matrix.

    The user selects 2 dates and the database table returns all data/users between those dates.

    Here is my SQL...

    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    
    SET @StartDate = '2012/03/01'
    SET @EndDate = '2012/03/15'
    
    WHILE(@StartDate <= @EndDate)
    BEGIN
     SELECT username, totalValue
     FROM table1
     WHERE userdate = @selectedDate
    
     SET @StartDate = DATEADD(DAY, 1, @StartDate)
    END

    And my desired output is...
    Username ---  Mar 1  ----  Mar 2 --- Mar 3 --- Mar 4 --- Mar 5 --- Mar 6...
    userA     -----   23    ----   56   ---     89   ----  7      ---  45     ---  30...

    How do I get the desired results?

    Thanks!

    Thursday, March 15, 2012 5:39 PM

Answers

  • You can not use table variable defined outside the batch in dynamic SQL. Instead of @temp table variable switch to #temp temp table. It will be visible by dynamic SQL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by obrienkev Friday, March 16, 2012 4:30 PM
    Friday, March 16, 2012 1:08 PM
  • Hi,

    Here is my sample data...

    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    
    SET @StartDate = '2012/03/01'
    SET @EndDate = '2012/03/03'
    
    DECLARE @UserID nvarchar(4)
    DECLARE @Perc int
    DECLARE @count int
    
    SET @UserID = 'User120'
    SET @Perc = 23;
    SET @count = 1
    
    DECLARE @Temp as table
    (
    	UserID nvarchar(5),
    	Period nvarchar(12),
    	Perc int
    )
    WHILE(@StartDate <= @EndDate)
    BEGIN
    	INSERT INTO @Temp(UserID, Period, Perc)
    	VALUES(@UserID, CONVERT(nvarchar(12),@StartDate,103), @Perc)
    	
    	SET @StartDate = DATEADD(DAY, 1, @StartDate)	
    	set @Perc = sum(@Perc + 7)
    	set @UserID = @UserID + cast(@count as nvarchar)
    	set @count = @count + 1
    END

    And my Pivot code...

    DECLARE @cols NVARCHAR(2000)
    SELECT  @cols = COALESCE(@cols + ',[' + Period + ']','[' + Period + ']')
    FROM    @Temp
    ORDER BY Period
    
    DECLARE @query NVARCHAR(max)
    SET @query = N'SELECT UserID, '+
    @cols +'
    FROM
    (SELECT  UserID
          , Period
          , Perc
    FROM    @Temp) p
    PIVOT
    (
    MAX([Perc])
    FOR Period IN
    ( '+
    @cols +' )
    ) AS pvt
    ORDER BY UserID;'
    
    EXECUTE(@query)

    But get error... Must declare the table variable "@Temp"

    What have I wrong? Thanks!

    • Marked as answer by obrienkev Friday, March 16, 2012 4:31 PM
    Friday, March 16, 2012 10:46 AM
  • This works. Thanks for help!!

    	DECLARE @cols NVARCHAR(2000)
    	SELECT  @cols = COALESCE(@cols + ',[' + Period + ']','[' + Period + ']')
    	FROM    #temp GROUP BY Period
    	ORDER BY Period

    For anyone you may be interested this was also very helpful...

    http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

    • Marked as answer by obrienkev Friday, March 16, 2012 4:30 PM
    • Edited by obrienkev Friday, March 16, 2012 4:32 PM
    Friday, March 16, 2012 4:30 PM

All replies

  • If you want to make this print as a table with a dynamic number and names of columns, you have to use dynamic SQL.  If you can limit the number of days to some reasonable maximum (like two weeks), you could make a parameterized view to display this data and then render the headers in your calling application.  Is this going to be displayed as script output in sqlcmd or SSMS, or are you going to use this in some other application?
    • Proposed as answer by vinaypugalia Friday, March 16, 2012 4:08 AM
    Thursday, March 15, 2012 7:29 PM
  • try

    declare @table table (username varchar(50),userdate date, totalvalue int)
    insert @table(username,userdate, totalvalue)
    values('userA','20120301',12),('userA','20120302',20),('userA','20120303',35),('userA','20120315',40)
    
    
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    
    SET @StartDate = '20120301'
    SET @EndDate = '20120315'
    
     SELECT username, totalValue, CONVERT(varchar(6),userdate,100) as monday
     FROM @table
     WHERE userdate between @StartDate and @EndDate
     
    select username, [Mar  1],[mar  2],[mar  3], [Mar 15]  from (
     SELECT username, totalValue, CONVERT(varchar(6),userdate,100) as monday
     FROM @table
     WHERE userdate between @StartDate and @EndDate) p
     pivot
     (sum(totalvalue) for monday in ([Mar  1],[Mar  2],[Mar  3],[Mar 15])
     ) as pvt
     
     

    Maybe you have to do the pivot dynamically and maybe, if you need it, create missing days by using a num table or cte.

    Thursday, March 15, 2012 7:45 PM
  • an example with dynamic sql:

    create table ##table (username varchar(50),userdate date, totalvalue int)
    go
    insert ##table(username,userdate, totalvalue) values('userB','20120306',12),
    ('userA','20120301',12),('userA','20120302',20),('userA','20120303',35),('userA','20120315',40)
    
    create table ##numtable(nr int)
    go
    ;with cte as (
    select 1 as nr
    union all
    select nr + 1 from cte
    where nr < 100)
    insert into ##numtable select * from cte
    
    
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    declare @list varchar(2000)
    declare @sql varchar(2000)
    
    SET @StartDate = '20120229'
    SET @EndDate = '20120315'
    
    ;with cte2 as (
     SELECT username, totalValue, CONVERT(varchar(6),calcdate,100) as monday
     FROM ##table right join (select @StartDate + nr as calcdate from ##numtable) a on
     userdate = calcdate
     WHERE calcdate between @StartDate and @EndDate
     )
     
    select @list = stuff((select ',' + '[' + monday + ']' from cte2 for xml path('')),1,1,'')
    
    set @sql = '
    select username, ' + @list + ' from (
     SELECT username, totalValue, CONVERT(varchar(6),calcdate,100) as monday
     FROM ##table right join (select dateadd(day,nr,''' + convert(char(8),@StartDate,112) + ''' ) calcdate from ##numtable
     ) a on
     userdate = calcdate
     WHERE calcdate between ''' + convert(char(8),@StartDate,112) + ''' and ''' + convert(char(8),@EndDate,112) + ''') p
     pivot
     (sum(totalvalue) for monday in (' + @list + ')
     ) as pvt'
     exec ( @sql )
     

    Thursday, March 15, 2012 8:42 PM
  • Do you want to display all dates as headers or only dates that have data? For the first case, you need to expand the range first into individual date between dates (best way using calendar table). Then LEFT JOIN with the table with data and dynamically pivot it.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 15, 2012 10:07 PM
  • The concept of a Matrix is not part of relational databases. A metric is not the smawe as an array, either.  Why are you not using ISO-8601 data display formats? 

    Why are you formatting data in the back end?  The basic principle of a tiered architecture is that display is done in the front end and never in the back end.  This is a more basic programming principle than just SQL and RDBMS. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 16, 2012 3:59 AM
  • Hi,

    Here is my sample data...

    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    
    SET @StartDate = '2012/03/01'
    SET @EndDate = '2012/03/03'
    
    DECLARE @UserID nvarchar(4)
    DECLARE @Perc int
    DECLARE @count int
    
    SET @UserID = 'User120'
    SET @Perc = 23;
    SET @count = 1
    
    DECLARE @Temp as table
    (
    	UserID nvarchar(5),
    	Period nvarchar(12),
    	Perc int
    )
    WHILE(@StartDate <= @EndDate)
    BEGIN
    	INSERT INTO @Temp(UserID, Period, Perc)
    	VALUES(@UserID, CONVERT(nvarchar(12),@StartDate,103), @Perc)
    	
    	SET @StartDate = DATEADD(DAY, 1, @StartDate)	
    	set @Perc = sum(@Perc + 7)
    	set @UserID = @UserID + cast(@count as nvarchar)
    	set @count = @count + 1
    END

    And my Pivot code...

    DECLARE @cols NVARCHAR(2000)
    SELECT  @cols = COALESCE(@cols + ',[' + Period + ']','[' + Period + ']')
    FROM    @Temp
    ORDER BY Period
    
    DECLARE @query NVARCHAR(max)
    SET @query = N'SELECT UserID, '+
    @cols +'
    FROM
    (SELECT  UserID
          , Period
          , Perc
    FROM    @Temp) p
    PIVOT
    (
    MAX([Perc])
    FOR Period IN
    ( '+
    @cols +' )
    ) AS pvt
    ORDER BY UserID;'
    
    EXECUTE(@query)

    But get error... Must declare the table variable "@Temp"

    What have I wrong? Thanks!

    • Marked as answer by obrienkev Friday, March 16, 2012 4:31 PM
    Friday, March 16, 2012 10:46 AM
  • You can not use table variable defined outside the batch in dynamic SQL. Instead of @temp table variable switch to #temp temp table. It will be visible by dynamic SQL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by obrienkev Friday, March 16, 2012 4:30 PM
    Friday, March 16, 2012 1:08 PM
  • Hi,

    Issue now is I could have duplicate period values, which gives an error - "Coulmn 'mar 01' was specified multiple times for 'pvt'"

    sample data:

    UserID---------Period--------Perc
    userA-----------mar 01-------33
    UserB-----------mar 01-------12
    UserC-----------mar 12-------87

    Any ideas on solution for this?

    Thanks.

    Friday, March 16, 2012 3:05 PM
  • In your select where you define @Cols dynamically you need to select distinct periods.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, March 16, 2012 3:16 PM
  • This works. Thanks for help!!

    	DECLARE @cols NVARCHAR(2000)
    	SELECT  @cols = COALESCE(@cols + ',[' + Period + ']','[' + Period + ']')
    	FROM    #temp GROUP BY Period
    	ORDER BY Period

    For anyone you may be interested this was also very helpful...

    http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

    • Marked as answer by obrienkev Friday, March 16, 2012 4:30 PM
    • Edited by obrienkev Friday, March 16, 2012 4:32 PM
    Friday, March 16, 2012 4:30 PM