[Webinar] Streamline your web hosting managementRegister Today

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

Pivot Unpivot in SQL Server

Hello ,
Current Format of the Server table  shows the data structure it’s in currently.  Somehow, someway it has to be pivoted to get it in the format I need where time stamp columns transform to the rows instead.  The format I need is shown in Desired Format sheet. Whats the code in SQL Server that does that.
Thanks
pivotDesired.xlsx
0
Rayne
Asked:
Rayne
  • 5
  • 2
2 Solutions
 
lcohanDatabase AnalystCommented:
You can also do that using CROSS TAB if that's easier for you - like in example below:

http://www.sqlservercentral.com/articles/t-sql/63681/


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

--using crosstab
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

-- using pivot

--===== Use a Pivot to do the same thing we did with the Cross Tab
 SELECT Year,             --(4)
        [1] AS [1st Qtr], --(3)
        [2] AS [2nd Qtr],
        [3] AS [3rd Qtr],
        [4] AS [4th Qtr],
        [1]+[2]+[3]+[4] AS Total --(5)
   FROM (SELECT Year, Quarter,Amount FROM #test_table)  AS src --(1)
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt --(2)
  ORDER BY Year


--===== Converting NULLs to zero's in the Pivot using COALESCE
 SELECT Year,
        COALESCE([1],0) AS [1st Qtr],
        COALESCE([2],0) AS [2nd Qtr],
        COALESCE([3],0) AS [3rd Qtr],
        COALESCE([4],0) AS [4th Qtr],
        COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
   FROM (SELECT Year, Quarter,Amount FROM #test_table)  AS src
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt
  ORDER BY Year



--unpivot
create table #TableA (Period sysname, Col1 smallint, Col2 smallint, Col3 smallint)
insert into #tableA select 'Current',50,80,90
select * from #tableA


SELECT Period, Cols, Counts
FROM
(SELECT Period, Col1, Col2, Col3 FROM #tableA) p
UNPIVOT
(counts FOR Cols IN (Col1, Col2, Col3 )
)AS unpvt;
GO
0
 
RayneAuthor Commented:
Hello Everyone,

tHe last code is the only shortest way to do that? I am still learning SQL so :(
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
RayneAuthor Commented:
Great work guyx, let me know if there are shorter tsql code? But this looks good to me :)
If there is another way, let me know I will open another question for that.
0
 
RayneAuthor Commented:
Thank You :)
0
 
RayneAuthor Commented:
Hello Icohan,

When I just ran  this:
SELECT Year,             --(4)
        [1] AS [1st Qtr], --(3)
        [2] AS [2nd Qtr],
        [3] AS [3rd Qtr],
        [4] AS [4th Qtr],
        [1]+[2]+[3]+[4] AS Total --(5)
   FROM (SELECT Year, Quarter,Amount FROM Atest_table)  AS src --(1)
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt --(2)
  ORDER BY Year

Then I got this error:
Msg 325, Level 15, State 1, Line 8
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.


How can that be resolved? I am a admin…
0
 
RayneAuthor Commented:
please assist...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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