Solved

crosstab T-SQL

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

select  t1.id,  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
0
Comment
Question by:TClevel
[X]
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
3 Comments
 
LVL 8

Accepted Solution

by:
venk_r earned 250 total points
ID: 37738275
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37738628
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
0
 
LVL 12

Expert Comment

by:kselvia
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.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
When to use a Unique Index? A Clustered Index? 5 76
SQL profiler 3 51
Help with SQL pivot 11 49
Set a variable value in SQL Procedure 3 26
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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