Pivot Using SQL

Question

Pivot Using SQL

QQ-485619

Ten Centuries

Points: 1195
More actions
October 28, 2024 at 1:47 am

#4474333

I need some help with pivot.
For the following data set:

and produce an output like this:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Can you provide your sample data in consumable format, please?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

As Phil suggests, please provide the data in a readily consumable format instead of a graphic to help us help you. See the first link in my signature line below for one way to do that.

Also, what happens if you end up with more than 6 EntryDates?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

QQ-485619 Ten Centuries Points: 1195 More actions · Answer 3

Here is the script to create table:

CREATE TABLE [dbo].[Test](

[MemberID] [bigint] NULL,

[Performed_Date] [datetime] NULL,

[DateRank] [bigint] NULL

) ON [PRIMARY]

GO

Here is the script to populate the data:

insert into test (MemberID, Performed_Date, DateRank)

values

('168451', '2024-10-21 00:00:00.000', '1'),

('176124', '2024-10-25 00:00:00.000', '1'),

('178675', '2024-10-07 00:00:00.000', '1'),

('178675', '2024-10-14 00:00:00.000', '2'),

('178675', '2024-10-17 00:00:00.000', '3'),

('180455', '2024-10-10 00:00:00.000', '1'),

('180455', '2024-10-21 00:00:00.000', '2'),

('180455', '2024-10-25 00:00:00.000', '3'),

('322569', '2024-10-14 00:00:00.000', '1'),

('323092', '2024-10-03 00:00:00.000', '1'),

('323092', '2024-10-04 00:00:00.000', '2'),

('323092', '2024-10-15 00:00:00.000', '3'),

('323092', '2024-10-21 00:00:00.000', '4'),

('323092', '2024-10-22 00:00:00.000', '5'),

('323092', '2024-10-25 00:00:00.000', '6');

the DateRank can be up to 10.

Thank you very much for your help.

Ed B Hall of Fame Points: 3666 More actions · Answer 4

I always have to look up the pivot syntax so I usually use case statements, but this is an attempt at both.

--PIVOT
SELECT  b.MemberID, b.[1] AS EntryDate1, b.[2] AS EntryDate2, b.[3] AS EntryDate3, 
        b.[4] AS EntryDate4, b.[5] AS EntryDate5, b.[6] AS EntryDate6, b.[7] AS EntryDate7, 
        b.[8] AS EntryDate8, b.[9] AS EntryDate9, b.[10] AS EntryDate10
FROM 
(
  SELECT MemberID, Performed_Date, DateRank
  FROM dbo.Test
) AS a 
PIVOT  
(  
  MAX(a.Performed_Date)  
  FOR a.DateRank IN  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) 
) AS b;  


-- CASE STATEMENTS
SELECT  a.MemberID,
        MAX(CASE WHEN a.DateRank = 1 THEN a.Performed_Date ELSE NULL END) AS EntryDate1,
        MAX(CASE WHEN a.DateRank = 2 THEN a.Performed_Date ELSE NULL END) AS EntryDate2,
        MAX(CASE WHEN a.DateRank = 3 THEN a.Performed_Date ELSE NULL END) AS EntryDate3,
        MAX(CASE WHEN a.DateRank = 4 THEN a.Performed_Date ELSE NULL END) AS EntryDate4,
        MAX(CASE WHEN a.DateRank = 5 THEN a.Performed_Date ELSE NULL END) AS EntryDate5,
        MAX(CASE WHEN a.DateRank = 6 THEN a.Performed_Date ELSE NULL END) AS EntryDate6,
        MAX(CASE WHEN a.DateRank = 7 THEN a.Performed_Date ELSE NULL END) AS EntryDate7,
        MAX(CASE WHEN a.DateRank = 8 THEN a.Performed_Date ELSE NULL END) AS EntryDate8,
        MAX(CASE WHEN a.DateRank = 9 THEN a.Performed_Date ELSE NULL END) AS EntryDate9,
        MAX(CASE WHEN a.DateRank = 10 THEN a.Performed_Date ELSE NULL END) AS EntryDate10
FROM    dbo.Test AS a
GROUP   BY MemberID

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

First, thank you for helping us help you by posting the readily consumable data code.

Ed B.'s code is good, although I'll never use PIVOT because of the issue he pointed out and more (performance on big stuff and don't want to get into the habit of using it).

If you have to have blanks instead of NULLs, this will work. Also, using IIF() instead of CASE shortens up the code for the CROSSTAB (which is what this is called in the absence of a PIVOT) a bit. I also use the ColumnAlias = Expression form for such things because it makes the ColumnAlias names really easy to find (just in case you're wondering what I did and why :D)

Also, the conversion to CHAR() makes things come out right in both the GRID and TEXT modes and will probably work fine either way for dump into a spreadsheet.

 SELECT  a.MemberID
        ,EntryDate1  = MAX(IIF(a.DateRank =  1,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate2  = MAX(IIF(a.DateRank =  2,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate3  = MAX(IIF(a.DateRank =  3,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate4  = MAX(IIF(a.DateRank =  4,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate5  = MAX(IIF(a.DateRank =  5,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate6  = MAX(IIF(a.DateRank =  6,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate7  = MAX(IIF(a.DateRank =  7,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate8  = MAX(IIF(a.DateRank =  8,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate9  = MAX(IIF(a.DateRank =  9,CONVERT(CHAR(23),a.Performed_Date,121),''))
        ,EntryDate10 = MAX(IIF(a.DateRank = 10,CONVERT(CHAR(23),a.Performed_Date,121),''))
   FROM dbo.Test AS a
  GROUP BY MemberID
;

Results:

Since all of the times seem to be midnight, we can just switch to whole dates (note the change from CONVERT format 121 to 23 and the size change for the CHAR() )...

 SELECT  a.MemberID
        ,EntryDate1  = MAX(IIF(a.DateRank =  1,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate2  = MAX(IIF(a.DateRank =  2,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate3  = MAX(IIF(a.DateRank =  3,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate4  = MAX(IIF(a.DateRank =  4,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate5  = MAX(IIF(a.DateRank =  5,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate6  = MAX(IIF(a.DateRank =  6,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate7  = MAX(IIF(a.DateRank =  7,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate8  = MAX(IIF(a.DateRank =  8,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate9  = MAX(IIF(a.DateRank =  9,CONVERT(CHAR(10),a.Performed_Date,23),''))
        ,EntryDate10 = MAX(IIF(a.DateRank = 10,CONVERT(CHAR(10),a.Performed_Date,23),''))
   FROM dbo.Test AS a
  GROUP BY MemberID
;

Results:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

QQ-485619 Ten Centuries Points: 1195 More actions · Answer 6

QQ-485619

Ten Centuries

Points: 1195

October 30, 2024 at 1:29 am

#4475194

thank you very much,

Jian