Dynamically Generated Column headings in sql query

问题:

I have a user_log_table which has columns as

 Userid         logintime             browsername
   1        2013/10/01 00:10:10         Chrome 
   1        2013/10/01 17:23:10         Chrome 
   1        2013/10/01 00:30:41         Mozilla
   1        2013/10/02 05:10:52         IE
   3        2013/10/02 09:10:25         Chrome 
   3        2013/10/03 10:10:18         Safari
   1        2013/10/03 13:10:35         Chrome 

I want a query that should display the output as

  Userid   browser  01/10    02/10   03/10 
   1       Chrome     2       0       1
   1       Mozilla    1       0       0
   1       IE         0       1       0
   3       Chrome     0       1       0
   3       Safari     0       0       1

Please note that the Browsername are not fixed, its dynamic,...

I have tried with the query as

SELECT userid, browser, Day(logintime) as LoginDay, COUNT(logintime) as Num 
FROM   user_log_table  
GROUP BY  userid, browser, Day(logintime)

but didn't find the result as I want.. Can I get the Column Heading as above and the data too?

回答1:

I saw this dynamic pivot problem different times. And I had it to. After searching a lot I came to a solution taht works great for me. it isn't that elegant but it saved me. What I do is first prepare the data in a temporary table, after that I dynamically create the pivot string (in your case it's DD-MM) assigning it to a variable. And in the end I construct a sql string to execute as dynamic sql.

I Used your sample data and it seems to work. Hope this helps

select 
    userid,
         browsername, 
         CAST(day(logintime) as nvarchar(2)) + '-' + CAST(Month(logintime) AS nVARCHAR(2))  AS period  
INTO #TMP
from user_log_table order by 1

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' 
                    + QUOTENAME(convert(varchar(10), period, 120)) 
                from #TMP order by 1
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT userid, browsername,' + @cols + ' from 
         (
            select userid, browsername, period
            from #TMP
        ) x
        pivot 
        (
            count(period)
            for period in (' + @cols + ')
        ) p '
exec(@query)
drop table #TMP

回答2:

Something like the following or you can use this with PIVOT in MS SQL:

SQLFiddle demo

SELECT browsername,
       SUM(CASE WHEN DAY(logintime) = 1 THEN 1 ELSE 0 END) as [01/10],
       SUM(CASE WHEN DAY(logintime) = 2 THEN 1 ELSE 0 END) as [02/10],
       SUM(CASE WHEN DAY(logintime) = 3 THEN 1 ELSE 0 END) as [03/10],
       ...
       SUM(CASE WHEN DAY(logintime) = 31 THEN 1 ELSE 0 END) as [31/10]

FROM user_log_table
GROUP BY browsername

回答3:

DECLARE @cols VARCHAR(100),@SQL VARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(convert(varchar(10),LoginTime,103))
                      FROM 
                      user_log_table
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');
SET @SQL = 'SELECT *
FROM
(
  SELECT
    Userid,
    browsername,
    convert(varchar(10),LoginTime,103) LoginDay
  FROM user_log_table xx
) AS t
PIVOT 
(
  count(LoginDay) 
  FOR LoginDay IN( '+@cols+' ) ) AS p ; ' 

  EXEC (@SQL)