Is this possible with SQL Server

Question

I have a table which has events and the day they occurred:

Table 'Events':

Name  Day
-----------
A     1
B     2
A     2
B     3

I need output columns to be a date range based on query input with rows being which event happened on the day so:

Desired output:

Day-1 Day-2 Day-3
-----------------
A     A     -
-     B     B

If this is possible can anyone give me a sample query that could generate this output based on a date range. There are all sorts of I have no clue how to even approach this issues here like an unknown number of columns.

I think pivot command might help you https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — PaulF, May 11 '18 at 19:44

score 3 · Answer 1 · answered May 11 '18 at 19:38

3

You can use conditional aggregation:

select max(case when day = 1 then name end) as day_1,
       max(case when day = 2 then name end) as day_2,
       max(case when day = 3 then name end) as day_3       
from t
group by name;

Note: This returns NULL rather than -. I think NULL makes more sense.

answered May 11 '18 at 19:38

Gordon Linoff

1,242,037
58
646
786

If the - is desired then you can simply add else '-' eg: max(case when day = 1 then name else '-' end) as day_1, – jonyfries May 11 '18 at 19:51
I don't see how to accept a start/stop day range. This would require me to build a query for each number of days desired. – MikeO May 11 '18 at 20:00

DxTx · Accepted Answer · 2018-05-11T20:39:44.527

Try this one...

Table Script and Sample data

CREATE TABLE [TableName](
    [Name] [nvarchar](50) NULL,
    [Day] [int] NULL
) 

INSERT [TableName] ([Name], [Day]) VALUES (N'A', 1)
INSERT [TableName] ([Name], [Day]) VALUES (N'B', 2)
INSERT [TableName] ([Name], [Day]) VALUES (N'A', 2)
INSERT [TableName] ([Name], [Day]) VALUES (N'B', 3)

Query (dynamic PIVOT)

DECLARE @startDay AS INT;
DECLARE @endDay   AS INT;

SET @startDay = 1;
SET @endDay   = 3;  

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([day])
         FROM   TableName
         WHERE [Day] >= @startDay AND [Day] <= @endDay
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT '+ @cols +'
                                    FROM   (SELECT *, 
                                                   Dense_rank() OVER (ORDER BY NAME) AS dr 
                                            FROM   TableName) sq 
                                           PIVOT(Max([name]) 
                                                FOR [day] IN ('+ @cols +') ) pvt ';

EXECUTE(@query)

Output

+--------+---+--------+
|   1    | 2 |   3    |
+--------+---+--------+
| A      | A | (null) |
| (null) | B | B      |
+--------+---+--------+

Online Demo: http://www.sqlfiddle.com/#!18/c688b/8/0

If you also want to use custom column name, try this...

DECLARE @startDay AS INT;
DECLARE @endDay   AS INT;

SET @startDay = 1;
SET @endDay   = 3;  

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([day])
         FROM   TableName
         WHERE [Day] >= @startDay AND [Day] <= @endDay
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @colNames AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([day]) + ' AS Days' + CONVERT(NVARCHAR(MAX), [day])
         FROM   TableName
         WHERE [Day] >= @startDay AND [Day] <= @endDay
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =  'SELECT '+ @colNames +'
                                    FROM   (SELECT *, 
                                                   Dense_rank() OVER (ORDER BY NAME) AS dr 
                                            FROM   TableName) sq 
                                           PIVOT(Max([name]) 
                                                FOR [day] IN ('+ @cols +') ) pvt ';
EXECUTE(@query)

Output

+-------+-------+-------+
| Days1 | Days2 | Days3 |
+-------+-------+-------+
| A     | A     | NULL  |
| NULL  | B     | B     |
+-------+-------+-------+

Online Demo: http://www.sqlfiddle.com/#!18/c688b/9/0

I still don't see how to make the requested day range be an input. — MikeO, May 11 '18 at 20:20

Is this possible with SQL Server

2 Answers2