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.
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_TESTGROUP BY CustomerID, ProductCodeHAVING ProductCode = 'P'GOSELECT CustomerID ,ProductCode ,MAX(NextPurchaseDate) As W_Purchase_Date FROM TBL_TESTGROUP BY CustomerID, ProductCodeHAVING ProductCode = 'W'GOSELECT CustomerID ,ProductCode ,MAX(NextPurchaseDate) As D_Purchase_Date FROM TBL_TESTGROUP BY CustomerID, ProductCodeHAVING ProductCode = 'D'GOIt yields the results:CustomerID--ProductCode-P_Purchase_DateAAAAAA------P-----------2012-11-17 BBBBBB------P-----------2012-08-12 CustomerID--ProductCode-W_Purchase_DateAAAAAA------W-----------2012-10-22BBBBBB------W-----------2012-07-20CustomerID--ProductCode-D_Purchase_DateAAAAAA------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_DateAAAAAA---------2012-11-17-------2012-10-22-------2012-12-27BBBBBB---------2012-08-12-------2012-07-20-------2012-08-22Here 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TBL_TEST]( [CustomerID] [nvarchar](6) NULL, [ProductCode] [nvarchar](10) NULL, [NextPurchaseDate] [datetime] NULL) ON [PRIMARY]GOINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TBL_TEST]( [CustomerID] [nvarchar](6) NULL, [ProductCode] [nvarchar](10) NULL, [NextPurchaseDate] [datetime] NULL) ON [PRIMARY]GOINSERT [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_TESTPIVOT (MAX(NextPurchaseDate) FOR ProductCode IN ([P],[W],[D]))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 3Incorrect syntax near '('.quote: Originally posted by visakh16
SELECT *FROM TBL_TESTPIVOT (MAX(NextPurchaseDate) FOR ProductCode IN ([P],[W],[D]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TBL_TEST]( [CustomerID] [nvarchar](6) NULL, [ProductCode] [nvarchar](10) NULL, [NextPurchaseDate] [datetime] NULL) ON [PRIMARY]GOINSERT [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 3Incorrect syntax near '('.quote: Originally posted by visakh16
SELECT *FROM TBL_TESTPIVOT (MAX(NextPurchaseDate) FOR ProductCode IN ([P],[W],[D]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
It will work fine in SQL 2005 so far as compatibility level is 90 or above for the database------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|