Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Matrix Query

已答复 SQL Matrix Query

  • Thursday, March 15, 2012 5:39 PM
     
      Has Code
    Sign In to Vote
    0

    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!

     

All Replies

  • Thursday, March 15, 2012 7:29 PM
     
     Proposed
    Sign In to Vote
    1
    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:45 PM
     
      Has Code
    Sign In to Vote
    1

    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 8:42 PM
     
      Has Code
    Sign In to Vote
    1

    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 10:07 PM
    Moderator
     
     
    Sign In to Vote
    1
    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

     
  • Friday, March 16, 2012 3:59 AM
     
     
    Sign In to Vote
    0

    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 10:46 AM
     
     Answered Has Code
    Sign In to Vote
    0

    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 1:08 PM
    Moderator
     
     Answered
    Sign In to Vote
    1
    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 3:05 PM
     
      Has Code
    Sign In to Vote
    0

    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:16 PM
    Moderator
     
     
    Sign In to Vote
    1
    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 4:30 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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
    •