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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert row data to column

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 | FieldValue
S001 | StoreName | 100 Store
S001 | OwnerName | Blah
S001 | ..... | ...
S002 | StoreName | 150 Store
S002 | OwnerName | Me
S002 | ... | ...
...

to:
StoreCode | StoreName | Owner Name
S001 | 100 Store | Blah
S002 | 150 Store | Me

How 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
*
FROM
YourTable
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
Go to Top of Page

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]
GO

GO
INSERT [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, OwnerName
FROM
Test
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P

This is the result I got:

StoreName | OwnerName
Top100 | NULL
NULL |Shivprasad
Top200 |NULL
NULL |Chandra
Top300 |NULL
NULL |Riya

Expected:

StoreName | OwnerName
Top100 | Shivprasad
Top200 | Chandra
Top300 | Riya

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

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 Test
Group By StoreCode
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-06 : 12:07:36
you will get same result from PIVOT too


SELECT StoreName, OwnerName
FROM (SELECT StoreCode,[FieldName], [FieldValue]
FROM Test)t
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P


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

Go to Top of Page

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, OwnerName
FROM (SELECT StoreCode,[FieldName], [FieldValue]
FROM Test
)t
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P

This doesn't:

SELECT StoreName, OwnerName
FROM Test
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P

Neither does this:

SELECT StoreName, OwnerName
FROM (SELECT *
FROM Test
)t
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -