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

Accepted Solution

venk_r earned 250 total points
ID: 37738275
LVL 39

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now