crosstab T-SQL

Posted on 2012-03-19
Last Modified: 2012-03-21
How can I do a cross tab

select,  ti.value
from  table1 t1
left Join table2 t2
on t2.jobid = t1.jobid
and t2.QuestionID = 'type_run'
left Join ResumeDataODS..ResumeResponse rTarg (nolock)
where t1.ID = 'time_type'  AND
 t2.ID = 'type_run'

ID                  value                  id                       value
----------------      ----------------          -----------------         ------------  
time_type      Hour                  type_run            15.00
time_type      YEAR                  type_run             25,000
time_type      YEAR                  type_run             35,000
time_type      Hour                  type_run             12.00
time_type      YEAR                  type_run              68,000

how can I get these value column  on the same grid
15.00 , 25,000, 35,000, 12.00 68,000
Question by:TClevel
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

Accepted Solution

venk_r earned 250 total points
ID: 37738275
LVL 40

Assisted Solution

lcohan earned 250 total points
ID: 37738628

CREATE TABLE #test_table
        Company  VARCHAR(3),
        Year     SMALLINT,
        Quarter  TINYINT,
        Amount   DECIMAL(2,1),
        Quantity DECIMAL(2,1)
 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

--using crosstab
SELECT Company,
        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)

--===== Converting NULLs to zero's in the Pivot using COALESCE
        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
LVL 12

Expert Comment

ID: 37739966
It is not clear what you actually want because your example query could not have generated the results you gave;

ID                  value                  id                       value
----------------      ----------------          -----------------         ------------   
time_type      Hour                  type_run            15.00
time_type      YEAR                  type_run             25,000
time_type      YEAR                  type_run             35,000
time_type      Hour                  type_run             12.00
time_type      YEAR                  type_run              68,000

Open in new window

nor is it even legal T-SQL. I realize you were just sjowing mock-up data. rTarg is not even used.

venk_r and lochan both provided solutions which require you to know name the columns in advance, but I can imagine what you really want means you may not know what they are, nor how many of them there will be.

Presumably what you are asking for is really something like this;

jobid			time_type_type_run_hour[1] time_type_type_run_year[1] time_type_type_run_year[2] time_type_type_run_hour[2] time_type_type_run_year[3]
--------------- -------------------------- -------------------------- -------------------------- -------------------------- --------------------------
1               15.00                      25,000                     35,000                     12.00                      68,000                                        

Open in new window

but you can see the column headings must be named something that uniquely identifies the row from which the data came.  Or perhaps you even want some row values aggregated.  In the example above, notice where I have had to number the duplicated column headings with [1], [2], [3] to keep them unique?  You could generate exactly the results above, and dynamically create aditional numbered columns as your pivoted rowcount increased, but even then, the question is; what do the column values even mean? The are in no particular order, and have no particular relationship to one another.  And some jobid's may have 10 results, while others have 3.

All can be handled, but you need to be clearer about what you are expecting.

Or maybe you don't actually want the numbers listed as seperate columns.  Maybe you just need 2 columns

Jobid           time_run_hour_year_list
--------------- --------------------------------------------------------------------
1               15.00 | 25,000 | 35,000 | 12.00  | 68,000  

Open in new window

which will be simpler to do than anything so far.

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

635 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