Solved

convert rows to columns

Posted on 2011-09-22
6
240 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
  • 3
  • 2
6 Comments
 
LVL 39

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 39

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 37
Sql server function help 15 28
What's wrong with this T-SQL Foreign Key? 7 41
Query group by data in SQL Server - cursor? 3 29
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

786 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