Turning Groups into Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Turning Groups into Columns

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-11-14 : 20:46:49

[code]
I have a table, TBL_TEST that contains the following data:

CustID--ProductCode--NextPurchaseDate
-------------------------------------
AAAAAA--P------------2012-11-17------
AAAAAA--P------------2012-09-28------
AAAAAA--W------------2012-10-05------
AAAAAA--W------------2012-10-22------
AAAAAA--D------------2012-12-16------
AAAAAA--D------------2012-12-27------
BBBBBB--P------------2012-08-12------
BBBBBB--P------------2012-06-26------
BBBBBB--W------------2012-07-20------
BBBBBB--W------------2012-07-05------
BBBBBB--D------------2012-08-22------
BBBBBB--D------------2012-08-03------

I wrote the following script to find the latest date for each customer ID and each product code:

SELECT CustomerID
,ProductCode
,MAX(NextPurchaseDate) As P_Purchase_Date
FROM TBL_TEST
GROUP BY CustomerID, ProductCode
HAVING ProductCode = 'P'
GO

SELECT CustomerID
,ProductCode
,MAX(NextPurchaseDate) As W_Purchase_Date
FROM TBL_TEST
GROUP BY CustomerID, ProductCode
HAVING ProductCode = 'W'
GO

SELECT CustomerID
,ProductCode
,MAX(NextPurchaseDate) As D_Purchase_Date
FROM TBL_TEST
GROUP BY CustomerID, ProductCode
HAVING ProductCode = 'D'
GO

It yields the results:

CustomerID--ProductCode-P_Purchase_Date
AAAAAA------P-----------2012-11-17
BBBBBB------P-----------2012-08-12

CustomerID--ProductCode-W_Purchase_Date
AAAAAA------W-----------2012-10-22
BBBBBB------W-----------2012-07-20

CustomerID--ProductCode-D_Purchase_Date
AAAAAA------D-----------2012-12-27
BBBBBB------D-----------2012-08-22

But I want to modify it further so I will get one row per customer ID, and the dates returned will appear as their own columns.
I want it to look like this, but I'm not sure how to do that. Any help would be appreciated.

CustomerID-----P_Purchase_Date--W_Purchase_Date--D_Purchase_Date
AAAAAA---------2012-11-17-------2012-10-22-------2012-12-27
BBBBBB---------2012-08-12-------2012-07-20-------2012-08-22

Here is a script to create the data:

USE [Northwind]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/14/2012 15:51:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[CustomerID] [nvarchar](6) NULL,
[ProductCode] [nvarchar](10) NULL,
[NextPurchaseDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'P', CAST(0x0000A10C00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'P', CAST(0x0000A0DA00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'W', CAST(0x0000A0E100000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'W', CAST(0x0000A0F200000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'D', CAST(0x0000A12900000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'D', CAST(0x0000A13400000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'P', CAST(0x0000A0AB00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'P', CAST(0x0000A07C00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'W', CAST(0x0000A09400000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'W', CAST(0x0000A08500000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'D', CAST(0x0000A0B500000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'D', CAST(0x0000A0A200000000 AS DateTime))

[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-14 : 23:23:55

[code]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[CustomerID] [nvarchar](6) NULL,
[ProductCode] [nvarchar](10) NULL,
[NextPurchaseDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'P', CAST(0x0000A10C00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'P', CAST(0x0000A0DA00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'W', CAST(0x0000A0E100000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'W', CAST(0x0000A0F200000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'D', CAST(0x0000A12900000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'D', CAST(0x0000A13400000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'P', CAST(0x0000A0AB00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'P', CAST(0x0000A07C00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'W', CAST(0x0000A09400000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'W', CAST(0x0000A08500000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'D', CAST(0x0000A0B500000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'D', CAST(0x0000A0A200000000 AS DateTime))

Select [CustomerID],
MAX(Case When ProductCode = 'P' then [NextPurchaseDate] Else null End) as [P_Purchase_Date],
MAX(Case When ProductCode = 'W' then [NextPurchaseDate] Else null End) as [W_Purchase_Date],
MAX(Case When ProductCode = 'D' then [NextPurchaseDate] Else null End) as [D_Purchase_Date]
from [TBL_TEST]
Group by [CustomerID]
Order by [CustomerID][/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 05:35:25

[code]
SELECT *
FROM TBL_TEST
PIVOT (MAX(NextPurchaseDate) FOR ProductCode IN ([P],[W],[D]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-11-15 : 17:08:44

This is an intriguing solution, and it works great in SQL 2008, but I found when run in SQL 2005 it gives this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

quote:
Originally posted by visakh16

SELECT *
FROM TBL_TEST
PIVOT (MAX(NextPurchaseDate) FOR ProductCode IN ([P],[W],[D]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-11-15 : 17:10:09

Thanks for your solution - it worked great. I'll make a note of the technique so I can use it in the future.

quote:

Originally posted by sodeep


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
	[CustomerID] [nvarchar](6) NULL,
	[ProductCode] [nvarchar](10) NULL,
	[NextPurchaseDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'P', CAST(0x0000A10C00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'P', CAST(0x0000A0DA00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'W', CAST(0x0000A0E100000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'W', CAST(0x0000A0F200000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'D', CAST(0x0000A12900000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'AAAAAA', N'D', CAST(0x0000A13400000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'P', CAST(0x0000A0AB00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'P', CAST(0x0000A07C00000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'W', CAST(0x0000A09400000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'W', CAST(0x0000A08500000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'D', CAST(0x0000A0B500000000 AS DateTime))
INSERT [dbo].[TBL_TEST] ([CustomerID], [ProductCode], [NextPurchaseDate]) VALUES (N'BBBBBB', N'D', CAST(0x0000A0A200000000 AS DateTime))


Select [CustomerID],
MAX(Case When ProductCode = 'P' then [NextPurchaseDate] Else null End) as [P_Purchase_Date],
MAX(Case When ProductCode = 'W' then [NextPurchaseDate] Else null End) as [W_Purchase_Date],
MAX(Case When ProductCode = 'D' then [NextPurchaseDate] Else null End) as [D_Purchase_Date]
from [TBL_TEST]
Group by [CustomerID]
Order by [CustomerID]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 22:09:34

quote:
Originally posted by BobRoberts

This is an intriguing solution, and it works great in SQL 2008, but I found when run in SQL 2005 it gives this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

quote:
Originally posted by visakh16

SELECT *
FROM TBL_TEST
PIVOT (MAX(NextPurchaseDate) FOR ProductCode IN ([P],[W],[D]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It will work fine in SQL 2005 so far as compatibility level is 90 or above for the database

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources