Author |
Topic |
andy_iimc
Starting Member
5 Posts |
Posted - 2012-10-05 : 09:02:13
|
Hi, I require to convert data in the following structure:StoreCode | FieldName | FieldValueS001 | StoreName | 100 StoreS001 | OwnerName | BlahS001 | ..... | ... S002 | StoreName | 150 StoreS002 | OwnerName | MeS002 | ... | ......to:StoreCode | StoreName | Owner NameS001 | 100 Store | BlahS002 | 150 Store | MeHow do I go about doing this? I couldn't get the Case-When-Then-End to work. Any help deeply appreciated! |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-05 : 10:21:51
|
If you have only those two columns (or a set of known columns) that you want to pivot, you can use the PIVOT operator in SQL 2005 or later. The code would be like this:SELECT *FROMYourTable PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P If you don't know the columns in advance, you will need to use dynamic pivot. See Madhivanan's blog here: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx |
|
|
andy_iimc
Starting Member
5 Posts |
Posted - 2012-10-06 : 03:14:00
|
I created the table Test and some data as follows:CREATE TABLE [dbo].[Test]( [StoreCode] [varchar](10) NULL, [FieldName] [varchar](20) NULL, [FieldValue] [varchar](50) NULL, [StoreAttrNo] [varchar](50) NULL, [NewFieldValue] [varchar](50) NULL, [Flag] [int] NULL, [Report] [int] NULL) ON [PRIMARY]GOGOINSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S001', N'StoreName', N'Top100', N'0', NULL, 2, 3)INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S001', N'OwnerName', N'Shivprasad', N'1', N'Shyam', NULL, NULL)INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S002', N'StoreName', N'Top200', N'0', NULL, NULL, 3)INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S002', N'OwnerName', N'Chandra', N'1', NULL, 2, 3)INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S003', N'StoreName', N'Top300', N'0', N'Top3', NULL, NULL)INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S003', N'OwnerName', N'Riya', N'1', N'Keshav', 2, 3)I ran the query as suggested:SELECT StoreName, OwnerNameFROMTestPIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))PThis is the result I got:StoreName | OwnerNameTop100 | NULLNULL |ShivprasadTop200 |NULLNULL |ChandraTop300 |NULLNULL |RiyaExpected:StoreName | OwnerNameTop100 | ShivprasadTop200 | ChandraTop300 | RiyaThanks for your response sunitabeck! I suppose I left my initial question incomplete. I have about 29 attributes in my actual table for every store code. Hence, it is not dynamic! I am using SQL Server 2008 R2. How do I get to the Expected results? |
|
|
andy_iimc
Starting Member
5 Posts |
Posted - 2012-10-06 : 05:25:00
|
This did the trick :)Select StoreCode, MAX(Case When FieldName = 'StoreName' Then FieldValue Else Null End), MAX(Case When FieldName = 'OwnerName' Then FieldValue Else Null End)From TestGroup By StoreCode |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-06 : 12:07:36
|
you will get same result from PIVOT tooSELECT StoreName, OwnerNameFROM (SELECT StoreCode,[FieldName], [FieldValue]FROM Test)tPIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
andy_iimc
Starting Member
5 Posts |
Posted - 2012-10-08 : 01:56:10
|
You are spot on Visakh - your query worked as well! Follow up question from me .. why is that additional Inner query required? This works:SELECT StoreName, OwnerNameFROM (SELECT StoreCode,[FieldName], [FieldValue]FROM Test)tPIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))PThis doesn't:SELECT StoreName, OwnerNameFROM TestPIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))PNeither does this:SELECT StoreName, OwnerNameFROM (SELECT *FROM Test)tPIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-08 : 20:14:48
|
because it involves other columns which has unique values (in this case StrAttrNo) which causes additional rows to appear in output------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|