SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Turning Groups into Columns		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Yak Posting Veteran

USA
97 Posts
Posted - 11/14/2012 :  20:46:49  Show Profile  Reply with Quote 

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))

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/14/2012 :  23:23:55  Show Profile  Reply with Quote 

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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 11/15/2012 :  05:35:25  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

BobRoberts
Yak Posting Veteran

USA
97 Posts
Posted - 11/15/2012 :  17:08:44  Show Profile  Reply with Quote
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/
Go to Top of Page

BobRoberts
Yak Posting Veteran

USA
97 Posts
Posted - 11/15/2012 :  17:10:09  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 11/15/2012 :  22:09:34  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000