CODEWITHSUNDEEP
sql-servert-sql
mak101
mak101

Reputation: 147

How to convert row data into column name?

I have some data by date and need to summarize by month. I think I can use PIVOT function in sql server 2012 but I cant get this right.

For simplicity purpose tables are called details and summary, details has visitor counts by month and gate. Summary table has Gate, Visitor type and 12 columns for each month.

DECLARE @tblDetails TABLE (
    Gate varchar(15), EOM Date, TotVisitors decimal(10,2), TotKids decimal(10,2)
);

DECLARE @tblSumm TABLE (
    Gate varchar(15), CatDesc varchar(50),
    Jan decimal(10,2), Feb decimal(10,2), Mar decimal(10,2), 
    Apr decimal(10,2), May decimal(10,2), Jun decimal(10,2),
    Jul decimal(10,2), Aug decimal(10,2), Sep decimal(10,2), 
    Oct decimal(10,2), Nov decimal(10,2), Dec decimal(10,2)
);

insert into @tblDetails select 'East', '1/31/2018', 1000, 350
insert into @tblDetails select 'East', '2/28/2018', 990,225
insert into @tblDetails select 'East', '3/31/2018', 970, 390
insert into @tblDetails select 'East', '4/30/2018', 977, 290
insert into @tblDetails select 'East', '5/31/2018', 960, 375
insert into @tblDetails select 'East', '6/30/2018', 1020, 425
insert into @tblDetails select 'East', '7/31/2018', 1117, 450
insert into @tblDetails select 'East', '8/31/2018', 1090, 443
insert into @tblDetails select 'East', '9/30/2018', 980, 210
insert into @tblDetails select 'East', '10/31/2018', 960, 190
insert into @tblDetails select 'East', '11/30/2018', 990, 195
insert into @tblDetails select 'East', '12/31/2018', 1020, 330

insert into @tblDetails select 'West', '1/31/2018', 992, 333
insert into @tblDetails select 'West', '2/28/2018', 980, 265
insert into @tblDetails select 'West', '3/31/2018', 1005, 397
insert into @tblDetails select 'West', '4/30/2018', 960, 265
insert into @tblDetails select 'West', '5/31/2018', 982, 344
insert into @tblDetails select 'West', '6/30/2018', 1017, 399
insert into @tblDetails select 'West', '7/31/2018', 1080, 442
insert into @tblDetails select 'West', '8/31/2018', 1045, 413
insert into @tblDetails select 'West', '9/30/2018', 940, 217
insert into @tblDetails select 'West', '10/31/2018', 925, 302
insert into @tblDetails select 'West', '11/30/2018', 937, 287
insert into @tblDetails select 'West', '12/31/2018', 958, 271

insert into @tblDetails select 'North', '5/31/2018', 780, 380
insert into @tblDetails select 'North', '6/30/2018', 810, 400
insert into @tblDetails select 'North', '7/31/2018', 835, 411
insert into @tblDetails select 'North', '8/31/2018', 809, 415
insert into @tblDetails select 'North', '9/30/2018', 730, 390

insert into @tblDetails select 'South', '1/31/2018', 630, 210
insert into @tblDetails select 'South', '2/28/2018', 550, 190
insert into @tblDetails select 'South', '3/31/2018', 607, 215

This is what I want in my summary table:

Gate    CatDesc Jan Feb Mar Apr May     Jun July    Aug Sep Oct Nov Dec
East    Number of Visitors  1000    990 970 977 960 1020    1117    1090    980 960 990 1020
East    Number of Children  350 225 390 290 375 425 450 443 210 190 195 330
East    % of Children   35.00   22.73   40.21   29.68   39.06   41.67   40.29   40.64   21.43   19.79   19.70   32.35
West    Number of Visitors  992 980 1005    960 982 1017    1080    1045    940 925 937 958
West    Number of Children  333 265 397 265 344 399 442 413 217 302 287 271
West    % of Children   33.57   27.04   39.50   27.60   35.03   39.23   40.93   39.52   23.09   32.65   30.63   28.29
North   Number of Visitors  0   0   0   0   0   810 835 809 730 0   0   0
North   Number of Children  0   0   0   0   0   400 411 415 390 0   0   0
North   % of Children   0   0   0   0   0   49.38   49.22   51.30   53.42   0   0   0
South   Number of Visitors  630 550 607 0   0   0   0   0   0   0   0   0
South   Number of Children  210 190 215 0   0   0   0   0   0   0   0   0
South   % of Children   33.33   34.55   35.42   0   0   0   0   0   0   0   0   0

Upvotes: 1

Views: 107

Answers (1)

Thom A
Thom A

Reputation: 95544

I personally prefer using as Cross Tab to using the PIVOT operator. You need need to use a VALUES clause to get your Description, and then you can use the Cross tab to get the values:

DECLARE @Year int = 2018;

SELECT D.Gate,
       V.CatDesc,
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 1,1)) THEN V.KPI END),0) AS [Jan],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 2,1)) THEN V.KPI END),0) AS [Feb],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 3,1)) THEN V.KPI END),0) AS [Mar],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 4,1)) THEN V.KPI END),0) AS [Apr],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 5,1)) THEN V.KPI END),0) AS [May],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 6,1)) THEN V.KPI END),0) AS [Jun],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 7,1)) THEN V.KPI END),0) AS [Jul],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 8,1)) THEN V.KPI END),0) AS [Aug],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 9,1)) THEN V.KPI END),0) AS [Sep],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 10,1)) THEN V.KPI END),0) AS [Oct],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 11,1)) THEN V.KPI END),0) AS [Nov],
       ISNULL(MAX(CASE WHEN D.EOM = EOMONTH(DATEFROMPARTS(@Year, 12,1)) THEN V.KPI END),0) AS [Dec]
FROM @tblDetails D                                                                     
     CROSS APPLY(VALUES(1,'Number of Visitors',D.TotVisitors),
                       (2,'Number of Children',D.TotKids),
                       (3,'% of Children',CONVERT(decimal(8,2),D.TotKids / (D.TotVisitors *1.0)*100)))V(Ordering,CatDesc,KPI)
GROUP BY D.Gate,
         V.CatDesc,
         V.Ordering
ORDER BY D.Gate,
         V.Ordering;

Note that a column in SQL Server can only be one data type, so all values returned are a decimal(8,2).

Upvotes: 2

Related Questions