Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

convert rows to columns

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
SQL101
Asked:
SQL101
  • 3
  • 2
2 Solutions
 
lcohanDatabase AnalystCommented:
You can do that by using PIVOT and UNPIVOT:

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 
lcohanDatabase AnalystCommented:
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!

 
venk_rCommented:
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
 
VENKAT KOKULLASQL Server DBACommented:
The below link will surely helpout you:

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

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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now