Solved

convert rows to columns

Posted on 2011-09-22
6
243 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 250 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Assisted Solution

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 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