Display data where GROUP BY column are the column names

Question

I don't have much experience with dynamic sql, and I have no idea where to begin.

Table [Sales] has data that looks like the following:

    Name    Date             Hr     Min    Amt
    Joe         20150320     08     00     5
    Joe         20150320     08     15     3
    Carl        20150320     09     30     1
    Carl        20150320     09     45     2
    Ray         20150320     13     00     8
    Ray         20150320     13     30     6

A simple GROUP BY [Name], [Date], [Hr] would display the total by hour for each salesman.

Assuming the sample above is actually all the data in the table, how would the dynamic sql look like so that the column names are actually the column [Hour]? In the case above, the resultset would be composed of 4 columns: [Name], [Date], [08], [09], [13].

Thanks.

---

VM

Answer 1

what you are looking for is dynamic pivot...

refer this link that explains how it can be done. http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server

---

Hope it Helps!!

Answer 2

I believe this is what you are after based on your description. Here it is with the temp table and data (include that next time please)

if OBJECT_ID('tempdb..#temp') is not null drop table #temp
CREATE TABLE #temp
(
name varchar(10),
[Date] varchar(10),
hr int,
[Min] int,
Amt int
)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Joe','20150320',08,00,5)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Joe','20150320',08,15,3)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Carl','20150320',09,30,1)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Carl','20150320',09,45,2)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Ray','20150320',13,00,8)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Ray','20150320',13,30,6)

declare @pivot nvarchar(max)
      set @pivot = ''
      
select @pivot = @pivot + ',[' + convert(nvarchar(2000), t.hr) + ']'
from
      (
      select distinct
            hr
      from #temp
      ) t
      order by hr asc
  
if(LEN(@pivot) != 0)
begin 
      select @pivot = right(@pivot, len(@pivot) - 1)
end


declare @sql nvarchar(max) =
      '
      select
            *
      from
      (
select 
x.name,
x.[Date],
x.hr,
x.[Min],
x.Amt
from #temp x

      ) row
      pivot
      (
      max(hr)
      for hr in (' + @pivot + ')
      ) col
      '

--select @sql

exec sp_executesql @sql

Answer 3

Try this:

CREATE TABLE #table (name VARCHAR(20), date DATE, hr CHAR(2), min CHAR(20), amt INT)
INSERT INTO #table (name, date, hr, min, amt) VALUES
('Joe ', '20150320', '08', '00', 5),('Joe ', '20150320', '08', '15', 3),('Carl', '20150320', '09', '30', 1),
('Carl', '20150320', '09', '45', 2),('Ray ', '20150320', '13', '00', 8),('Ray ', '20150320', '13', '30', 6),
('Patrick', '20150320', '14', '30', 6),('Patrick', '20150320', '15', '30', 6),('Patrick', '20150320', '16', '30', 6)

DECLARE @dSQL NVARCHAR(MAX), @pivotCols NVARCHAR(MAX)

;WITH base AS (
SELECT CAST('['+hr+']' AS NVARCHAR(MAX)) AS string, ROW_NUMBER() OVER (ORDER BY CAST(hr AS INT) DESC) AS seq
  FROM #table
 GROUP BY hr
), rCTE AS (
SELECT string, seq
  FROM base
 WHERE seq = 1
UNION ALL
SELECT a.string +',' + r.string, a.seq
  FROM base a
    INNER JOIN rCTE r
	  ON r.seq + 1 = a.seq
)

SELECT @pivotCols = string
  FROM rCTE
 WHERE seq = (SELECT MAX(seq) FROM rCTE)

SET @dSQL = 'SELECT name, date, '+@pivotCols+'
  FROM (
        SELECT name, date, amt, hr
          FROM #table
       ) s
PIVOT (
       SUM(amt) FOR hr IN ('+@pivotCols+')
	  ) p
GROUP BY name, date, '+@pivotCols


EXEC sp_executeSQL @dSQL
DROP TABLE #table

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Answer 4

I had seen that link. The problem is that it doesn't GROUP BY.

---

VM

Answer 5

Ok, I see now. This should do it

--Note: GENERAL
if OBJECT_ID('tempdb..#temp') is not null drop table #temp
CREATE TABLE #temp
(
name varchar(10),
[Date] varchar(10),
hr int,
[Min] int,
Amt int
)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Joe','20150320',08,00,5)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Joe','20150320',08,15,3)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Carl','20150320',09,30,1)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Carl','20150320',09,45,2)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Ray','20150320',13,00,8)
insert into #temp(name, [Date], hr, [Min], Amt) values ('Ray','20150320',13,30,6)

declare @pivot nvarchar(max)
      set @pivot = ''
      
select @pivot = @pivot + ',[' + convert(nvarchar(2000), t.hr) + ']'
from
      (
      select distinct
            hr
      from #temp
      ) t
      order by hr asc
  
if(LEN(@pivot) != 0)
begin 
      select @pivot = right(@pivot, len(@pivot) - 1)
end


declare @sql nvarchar(max) =
      '
      select 
            *
      from
      (
select 
x.name,
x.[Date],
x.hr
from #temp x

      ) row
      pivot
      (
      sum(hr)
      for hr in (' + @pivot + ')
      ) col
      '

--select @sql

exec sp_executesql @sql

Answer 6

As a suggestion, after you figure this out: This isn't a scenario with endless possibilities, there are only 24 hours in a day, and chances are, you're looking at only 8 to 10 hours in a workday, so you could also do a pivot (the static kind, a.k.a. not dynamic) with the hours in the day you care about (in this particular example there would be empty hours).

EDIT:  Also, see this link on a similar topic and Erland's answer:  That often, when the number of pivoted columns is smaller, that it's often far more desirable to not use the PIVOT statement at all. Also, this approach would make it easier to have a catch-all "Other" column, perhaps "Midnight to 7 a.m.", 8, 9, 10... 5, 6, 7, "8pm to midnight" category.   https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5fd56fa-a508-4bbd-8cfd-27bb9c925172/sql-server-pivot-sum-by-day-grand-total?forum=transactsql  (Erland has articles on other reasons to dodge the Pivot statement when you can, visit his site).

Answer 7

So something like this... (add additional COUNT statements per hour)

Select * into #DEMOTABLE from 
( Values 
    ( 'Joe'       ,  '20150320'     , '8', '00',  5 )
   , ( 'Joe'       ,  '20150320'     , '8', '15',  3 )
   , ( 'Carl'      ,  '20150320'     , '9', '30',  1 )
   , ( 'Carl'      ,  '20150320'     , '9', '45',  2 )
   , ( 'Ray'       ,  '20150320'     , '13', '00',  8 )
   , ( 'Ray'       ,  '20150320'     , '13', '30',  6 )
 ) as VT(Name, Date, Hr, Min, Amt)

 Select Name, "date"
	, Count(case when HR between 0 and 7 then 'x' else null end) as Midnignt_to_7
	, Count(case when HR = '8' then 'x' else null end) as H8
	, Count(case when HR = '9' then 'x' else null end) as H9
	, Count(case when HR = '13' then 'x' else null end) as H13
	, Count(case when HR between '19' and '23' then 'x' else null end) as H7_to_Midnight
 from #DEMOTABLE	
 group by Name, Date

Output would look like: (minus the columns getting out of alignment)

Name	date	Midnignt_to_7	H8	H9	H13	H7_to_Midnight
Carl	20150320	0	0	2	0	0
Joe	20150320	0	2	0	0	0
Ray	20150320	0	0	0	2	0

Answer 8

And then finally... A way to do both...  First query lists all business hours (you could modify to show all 24 hours), and second relatively simple dynamic query can show just the hours where there were sales.  This could be rolled into a single query too, of course, but I bet after a week of realizing the columns in the dynamic query change every day, most people would want the more static list... so you're better off having it done in advance.

Select * into #DEMOTABLE from -- select * from == Drop table #DEMOTABLE
( Values 
    ( 'Joe'       ,  '20150320'     , '8', '00',  5 )
   , ( 'Joe'       ,  '20150320'     , '8', '15',  3 )
   , ( 'Carl'      ,  '20150320'     , '9', '30',  1 )
   , ( 'Carl'      ,  '20150320'     , '9', '45',  2 )
   , ( 'Ray'       ,  '20150320'     , '13', '00',  8 )
   , ( 'Ray'       ,  '20150320'     , '13', '30',  6 )
 ) as VT(Name, Date, Hr, Min, Amt)

 Select Name, "date"
	, Count(case when HR between 0 and 7 then 'x' else null end) as HM_to_7A
	, Count(case when HR = '8'  then 'x' else null end) as H8
	, Count(case when HR = '9'  then 'x' else null end) as H9
	, Count(case when HR = '10' then 'x' else null end) as H10
	, Count(case when HR = '11' then 'x' else null end) as H11
	, Count(case when HR = '12' then 'x' else null end) as H12
	, Count(case when HR = '13' then 'x' else null end) as H13
	, Count(case when HR = '14' then 'x' else null end) as H14
	, Count(case when HR = '15' then 'x' else null end) as H15
	, Count(case when HR = '16' then 'x' else null end) as H16
	, Count(case when HR = '17' then 'x' else null end) as H17
	, Count(case when HR = '18' then 'x' else null end) as H18
	, Count(case when HR = '19' then 'x' else null end) as H19
	, Count(case when HR between '19' and '23' then 'x' else null end) as H7P_to_M 
	into #DEMOSUMMARY
 from #DEMOTABLE	
 group by Name, Date
Select * from #DEMOSUMMARY

 Declare @DynSQL as NVarChar(Max)
 Set @DynSQL = 'Select Name, Date '
 If Exists (Select 'x' from #DemoSUmmary where HM_to_7A > 0) Set @DynSQL = @DynSQL + ', HM_to_7A'
 If Exists (Select 'x' from #DemoSummary where H8 > 0) Set @DynSQL = @DynSQL + ', H8'
 If Exists (Select 'x' from #DemoSummary where H9 > 0) Set @DynSQL = @DynSQL + ', H9'
 If Exists (Select 'x' from #DemoSummary where H10 > 0) Set @DynSQL = @DynSQL + ', H10'
 If Exists (Select 'x' from #DemoSummary where H11 > 0) Set @DynSQL = @DynSQL + ', H11'
 If Exists (Select 'x' from #DemoSummary where H12 > 0) Set @DynSQL = @DynSQL + ', H12'
 If Exists (Select 'x' from #DemoSummary where H13 > 0) Set @DynSQL = @DynSQL + ', H13'
 If Exists (Select 'x' from #DemoSummary where H14 > 0) Set @DynSQL = @DynSQL + ', H14'
 If Exists (Select 'x' from #DemoSummary where H15 > 0) Set @DynSQL = @DynSQL + ', H15'
 If Exists (Select 'x' from #DemoSummary where H16 > 0) Set @DynSQL = @DynSQL + ', H16'
 If Exists (Select 'x' from #DemoSummary where H17 > 0) Set @DynSQL = @DynSQL + ', H17'
 If Exists (Select 'x' from #DemoSummary where H18 > 0) Set @DynSQL = @DynSQL + ', H18'
 If Exists (Select 'x' from #DemoSummary where H7P_to_M > 0) Set @DynSQL = @DynSQL + ', H7P_to_M '
 Set @DynSQL = @DynSQL + ' From #DemoSummary'
 Select @DYNSQL
 Execute sp_ExecuteSQL @DynSQL