crosstab T-SQL

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
TClevelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
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
Ken SelviaRetiredCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.