Solved

SQL Query Pivot column help SQL 2012 Need Guru

Posted on 2013-11-21
6
297 Views
Last Modified: 2013-11-25
Hello all,

Okay hoping to find an example on how to accomplish this.   So I have a table called YearlyAnalysis that has the following columns:

CustomerID
Year
Revenue
TotalAmount
LeaseAmount
CurrentAssets
CurrentLiabilities

Sample there are other columns this is good enough.   So what I need to do is take the current Year (getdate()) and go back 3 years.   What I am trying to get is columns such as this let's say we are in 2013 right so this:

                                  2010       2011      2012     2013
Revenue
TotalAmount
LeaseAmount
CurrentAssets
CurrentLiabilities

If the year does not exist I still want a column for it with all nulls.  Can anyone provide me a sample way to do this?   I am using SQL Server 2012.
0
Comment
Question by:sbornstein2
6 Comments
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 100 total points
ID: 39667431
Try this:
select CustomerID, 'Revenue' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, Revenue from pivot_example ) t
pivot (sum(Revenue)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'TotalAmount' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, TotalAmount from pivot_example ) t
pivot (sum(TotalAmount)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'LeaseAmount' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, LeaseAmount from pivot_example ) t
pivot (sum(LeaseAmount)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'CurrentAssets' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, CurrentAssets from pivot_example ) t
pivot (sum(CurrentAssets)  for Year in ([2010],[2011],[2012],[2013])) as pvt

UNION
select CustomerID, 'CurrentLiabilities' as Metric, [2010],[2011],[2012],[2013]  from 
(SELECT CustomerID, Year, CurrentLiabilities from pivot_example ) t
pivot (sum(CurrentLiabilities)  for Year in ([2010],[2011],[2012],[2013])) as pvt

Open in new window

0
 

Author Comment

by:sbornstein2
ID: 39667842
I think this might work I am having one problem so what I am trying to do is instead of hardcoding the years I am doing this and year is an int field I have

select CustomerID, 'Revenue' as Metric, YEAR(GETDATE())-3,YEAR(GETDATE())-2,YEAR(GETDATE())-1,YEAR(GETDATE())  from
(SELECT CustomerID, Year, Revenue from pivot_example ) t
pivot (sum(Revenue)  for Year in (YEAR(GETDATE())-3,YEAR(GETDATE())-2,YEAR(GETDATE())-1,YEAR(GETDATE()))) as pvt

It does not like the GETDATE at all.  Is there a way to do this?  This way my query will never need changing for the years.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39667883
There's some bad news I'm afraid.

It is not possible to give those year columns names that change unless you use "dynamic sql". If you are prepared to have columns names like this:

Year -3   Year-2   Year-1   Current Year

Then you don't need "dynamic sql".

Do you absolutely need those column names to change?
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 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 39667928
Here is an approach without needing unions (& without dynamic column labels):
    CREATE TABLE YearlyAnalysis 
    ([CustomerID] int, [Year] int, [Revenue] int, [TotalAmount] int, [LeaseAmount] int, [CurrentAssets] int, [CurrentLiabilities] int)
;
    
INSERT INTO YearlyAnalysis 
    ([CustomerID], [Year], [Revenue], [TotalAmount], [LeaseAmount], [CurrentAssets], [CurrentLiabilities])
VALUES
    (1, 2013, 101, 102, 103, 104, 105),
    (1, 2012, 101, 102, 103, 104, 105),
    (1, 2011, 101, 102, 103, 104, 105),
    (1, 2010, 101, 102, 103, 104, 105),
    (1, 2009, 101, 102, 103, 104, 105)
;

**Query 1**:

SELECT
        label
      , SUM ( CASE WHEN [year] = ( datepart(YEAR,getdate()) ) - 3 THEN Value ELSE 0 END ) AS "Year - 3"
      , SUM ( CASE WHEN [year] = ( datepart(YEAR,getdate()) ) - 2 THEN Value ELSE 0 END ) AS "Year - 2"
      , SUM ( CASE WHEN [year] = ( datepart(YEAR,getdate()) ) - 1 THEN Value ELSE 0 END ) AS "Year - 1"
      , SUM ( CASE WHEN [year] =   datepart(YEAR,getdate())       THEN Value ELSE 0 END ) AS "Curent Year"
FROM (
      SELECT
              agr.[Year]
            , ca1.label
            , ca1.value
      FROM (
            SELECT
                    [Year]
                  , SUM ( Revenue ) AS Revenue
                  , SUM ( TotalAmount ) AS  TotalAmount
                  , SUM ( LeaseAmount ) AS  LeaseAmount
                  , SUM ( CurrentAssets ) AS  CurrentAssets
                  , SUM ( CurrentLiabilities ) AS  CurrentLiabilities
            FROM YearlyAnalysis
            WHERE [year] > = ( datepart(YEAR,getdate()) ) - 3
            GROUP BY [Year]
           ) AS agr
      CROSS APPLY (
                   VALUES
                        ('Revenue'           , agr.revenue)
                      , ('TotalAmount'       , agr.TotalAmount)
                      , ('LeaseAmount'       , agr.LeaseAmount)
                      , ('CurrentAssets'     , agr.CurrentAssets)
                      , ('CurrentLiabilities', agr.CurrentLiabilities)
                  ) AS ca1 (label, value)
     ) AS d
GROUP BY label
ORDER BY CASE
          WHEN label = 'Revenue' THEN 1
          WHEN label = 'TotalAmount' THEN 2
          WHEN label = 'LeaseAmount' THEN 3
          WHEN label = 'CurrentAssets' THEN 4
          WHEN label = 'CurrentLiabilities' THEN 5
          ELSE 99
         END
    
    


**[Results][2]**:

|              LABEL | YEAR - 3 | YEAR - 2 | YEAR - 1 | CURENT YEAR |
|--------------------|----------|----------|----------|-------------|
|            Revenue |      101 |      101 |      101 |         101 |
|        TotalAmount |      102 |      102 |      102 |         102 |
|        LeaseAmount |      103 |      103 |      103 |         103 |
|      CurrentAssets |      104 |      104 |      104 |         104 |
| CurrentLiabilities |      105 |      105 |      105 |         105 |



[1]: http://sqlfiddle.com/#!3/2ead5/15

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39667998
If you're willing to save the results into an intermediate table first, it's easy enough to rename the columns while still avoiding dynamic SQL.  Technically the order of the rows coming out of the last SELECT is not guaranteed, but since it's SELECTing from a heap and there are an extremely small number of rows, it's pretty much a lock that they will come out in the same, sorted order in which they were INSERTed.


USE master

IF OBJECT_ID('tempdb.dbo.#YearlyAnalysis') IS NOT NULL
    DROP TABLE #YearlyAnalysis
IF OBJECT_ID('tempdb.dbo.#YearlyAnalysis_Report') IS NOT NULL
    DROP TABLE #YearlyAnalysis_Report

CREATE TABLE #YearlyAnalysis (
    [CustomerID] int, [Year] smallint,
    [Revenue] int, [TotalAmount] int, [LeaseAmount] int, [CurrentAssets] int, [CurrentLiabilities] int
)

--TRUNCATE TABLE #YearlyAnalysis
INSERT INTO #YearlyAnalysis
    ( [CustomerID], [Year], [Revenue], [TotalAmount], [LeaseAmount], [CurrentAssets], [CurrentLiabilities] )
VALUES
    (1, 2013, 00064, 00128, 00256, 00512, 01024),
    (1, 2013, 00032, 00064, 00128, 00256, 00512),
    (1, 2012, 00016, 00032, 00064, 00128, 00256),
    (1, 2011, 00008, 00016, 00032, 00064, 00128),
    (1, 2010, 00004, 00008, 00016, 00032, 00064),
    (1, 2009, 00002, 00004, 00008, 00016, 00032),
    (1, 2008, 00001, 00002, 00004, 00008, 00016)
;

DECLARE @CustomerID int
DECLARE @EndingYear smallint

SET @CustomerID = 1
SET @EndingYear = YEAR(GETDATE())

-------------------------------------------------------------------------------

DECLARE @NewColumnName sysname

SELECT
    ColumnName,
    --optionally add other older year(s) here
    SUM(CASE WHEN Year = @EndingYear - 3 THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year - 3],
    SUM(CASE WHEN Year = @EndingYear - 2 THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year - 2],
    SUM(CASE WHEN Year = @EndingYear - 1 THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year - 1],
    SUM(CASE WHEN Year = @EndingYear     THEN CASE ColumnNumber
        WHEN 1 THEN Revenue
        WHEN 2 THEN TotalAmount
        WHEN 3 THEN LeaseAmount
        WHEN 4 THEN CurrentAssets
        WHEN 5 THEN CurrentLiabilities END ELSE 0 END) AS [Year]
INTO #YearlyAnalysis_Report
FROM #YearlyAnalysis
CROSS JOIN (
    SELECT 1 AS ColumnNumber, 'Revenue' AS ColumnName UNION ALL
    SELECT 2, 'TotalAmount' UNION ALL
    SELECT 3, 'LeaseAmount' UNION ALL
    SELECT 4, 'CurrentAssets' UNION ALL
    SELECT 5, 'CurrentLiabilities'
    --optionally add additional table columns here
) AS Columns
GROUP BY
    ColumnNumber,
    ColumnName
ORDER BY
    ColumnNumber

SET @NewColumnName = CAST(@EndingYear - 3 AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year - 3]', @NewColumnName, 'COLUMN'
SET @NewColumnName = CAST(@EndingYear - 2 AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year - 2]', @NewColumnName, 'COLUMN'
SET @NewColumnName = CAST(@EndingYear - 1 AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year - 1]', @NewColumnName, 'COLUMN'
SET @NewColumnName = CAST(@EndingYear AS varchar(4))
EXEC tempdb.sys.sp_rename '#YearlyAnalysis_Report.[Year]', @NewColumnName, 'COLUMN'

SELECT *
FROM #YearlyAnalysis_Report
0
 

Author Closing Comment

by:sbornstein2
ID: 39675892
these were all helpful actually thanks all
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax error with Dates where clause 10 49
SQL Dump exec output to table 3 22
SQL Group By Question 4 20
Shrink multiple databases at once 4 28
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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