?
Solved

convert rows to columns

Posted on 2011-09-22
6
Medium Priority
?
246 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
Comment
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

by:lcohan
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

by:venk_r
ID: 36581390
0
 
LVL 40

Accepted Solution

by:
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 8

Assisted Solution

by:venk_r
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

by:VENKAT KOKULLA
ID: 36585224
The below link will surely helpout you:

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

0
 
LVL 8

Expert Comment

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

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

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

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

Join & Ask a Question