Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# convert rows to columns

Posted on 2011-09-22
Medium Priority
248 Views
Last Modified: 2012-05-12
I have a table
USER_ID          PROPERTY_NAME       PROPERTY_VALUE
---------------------------------------------------------------------------
101                   NAME                          aaaaaaa
101                   Occupation                 aaaaaaa
101                   Married                        Y
101                   EmergencyContact      aaaaaaa
102                  NAME                          bbbbbbbb
101                   Occupation                bbbbbbbb
102                   Married                        N
102                  EmergencyContact      bbbbbbbb
103                   NAME                          cccccccc
103                  Occupation                cccccccc
103                 Married                        Y
103                 EmergencyContact     ccccccccc

How to get output as below with using pivot and with out using pivot

USER_ID    NAME       OCCUPATION   MARRIED  EMERGENCYCONTACT
101           aaaaa       aaaaaa             Y                 aaaaaa
102           bbbbb       bbbbbb             N                 bbbbbb
103           ccccc       cccccc            Y                 cccccc
0
Question by:SQL101
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2
6 Comments

LVL 40

Expert Comment

ID: 36581387
You can do that by using PIVOT and UNPIVOT:

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0

LVL 8

Expert Comment

ID: 36581390
0

LVL 40

Accepted Solution

lcohan earned 1000 total points
ID: 36581391
Or by using cross-tab like in example below:
CREATE TABLE #test_table
(
Company  VARCHAR(3),
Year     SMALLINT,
Quarter  TINYINT,
Amount   DECIMAL(2,1),
Quantity DECIMAL(2,1)
)
GO
INSERT INTO #test_table
(Company,Year, Quarter, Amount, Quantity)
SELECT 'ABC', 2006, 1, 1.1, 2.2 UNION ALL
SELECT 'ABC', 2006, 2, 1.2, 2.4 UNION ALL
SELECT 'ABC', 2006, 3, 1.3, 1.3 UNION ALL
SELECT 'ABC', 2006, 4, 1.4, 4.2 UNION ALL
SELECT 'ABC', 2007, 1, 2.1, 2.3 UNION ALL
SELECT 'ABC', 2007, 2, 2.2, 3.1 UNION ALL
SELECT 'ABC', 2007, 3, 2.3, 2.1 UNION ALL
SELECT 'ABC', 2007, 4, 2.4, 1.5 UNION ALL
SELECT 'ABC', 2008, 1, 1.5, 5.1 UNION ALL
SELECT 'ABC', 2008, 3, 2.3, 3.3 UNION ALL
SELECT 'ABC', 2008, 4, 1.9, 4.2 UNION ALL
SELECT 'XYZ', 2006, 1, 2.1, 3.6 UNION ALL
SELECT 'XYZ', 2006, 2, 2.2, 1.8 UNION ALL
SELECT 'XYZ', 2006, 3, 3.3, 2.6 UNION ALL
SELECT 'XYZ', 2006, 4, 2.4, 3.7 UNION ALL
SELECT 'XYZ', 2007, 1, 3.1, 1.9 UNION ALL
SELECT 'XYZ', 2007, 2, 1.2, 1.2 UNION ALL
SELECT 'XYZ', 2007, 3, 3.3, 4.2 UNION ALL
SELECT 'XYZ', 2007, 4, 1.4, 4.0 UNION ALL
SELECT 'XYZ', 2008, 1, 2.5, 3.9 UNION ALL
SELECT 'XYZ', 2008, 2, 3.5, 2.1 UNION ALL
SELECT 'XYZ', 2008, 3, 1.3, 3.9 UNION ALL
SELECT 'XYZ', 2008, 4, 3.9, 3.4
GO

SELECT Company,
Year,
SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
SUM(Amount)   AS TotalAmt,
SUM(Quantity) AS TotalQty
FROM #test_table
GROUP BY Company, Year
ORDER BY Company, Year
0

LVL 8

Assisted Solution

venk_r earned 1000 total points
ID: 36581626
This should do it

select DISTINCT PKEYS.USERID AS USERID, B.PROPERTY_VALUE AS NAME,C.PROPERTY_VALUE AS Occupation, D.PROPERTY_VALUE AS Married , E.PROPERTY_VALUE AS EmergencyContact
FROM
(SELECT USERID,PROPERTY_NAME FROM Property ) PKEYS
LEFT OUTER JOIN Property B ON PKEYS.USERID = B.USERID AND B.PROPERTY_NAME= 'Name'
LEFT OUTER JOIN Property C ON PKEYS.USERID = C.USERID AND C.PROPERTY_NAME= 'Occupation'
LEFT OUTER JOIN Property D ON PKEYS.USERID = D.USERID AND D.PROPERTY_NAME= 'Married'
LEFT OUTER JOIN Property E ON PKEYS.USERID = E.USERID AND E.PROPERTY_NAME= 'EmergencyContact'
0

LVL 5

Expert Comment

ID: 36585224
The below link will surely helpout you:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

0

LVL 8

Expert Comment

ID: 36588863
I think the link VENKAT1242 sent works for agreegate functions. Not sure if it will address your requirement.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month9 days, 9 hours left to enroll

#### 609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.