CODEWITHSUNDEEP
sql-servert-sqltranspose
Lennart
Lennart

Reputation: 1028

Transpose T-SQL query

I have a query like this:

SELECT 

SUM(
     CASE 
   WHEN 
START_DATE <= '2014-01-01' AND 
(END_DATE > '2014-01-01' OR END_DATE IS NULL)THEN 1 ELSE 0 END ) AS '201401',

SUM(
     CASE 
   WHEN 
START_DATE <= '2014-02-01' AND 
(END_DATE > '2014-02-01' OR END_DATE IS NULL)THEN 1 ELSE 0 END ) AS '201402')

    (etc)

FROM table

Gives me this as output:

201401  201402   (etc)
485     498

I need this to be transposed, e.g. give me this output:

201401  485
201402  498
(etc)

Examples I've found are all grouping the data; I just need to swap row and column.

Upvotes: 0

Views: 110

Answers (3)

GriGrim
GriGrim

Reputation: 2921

You'd better do this on client's side. Not in Db.

SELECT fields, x FROM (
    -- {{ your query here
    select 485 as [201401], 498 as [201402]
    -- }} your query here
) AS t
UNPIVOT (
    x FOR fields in ([201401], [201402])
) AS unpvt

Upvotes: 0

Jayvee
Jayvee

Reputation: 10873

you can transpose using union:

SELECT 
'201401',
SUM(
     CASE 
   WHEN 
START_DATE <= '2014-01-01' AND 
(END_DATE > '2014-01-01' OR END_DATE IS NULL)THEN 1 ELSE 0 END )
FROM table

UNION ALL

SELECT 
'201402',
SUM(
     CASE 
   WHEN 
START_DATE <= '2014-02-01' AND 
(END_DATE > '2014-02-01' OR END_DATE IS NULL)THEN 1 ELSE 0 END )
FROM table

UNION ALL

SELECT 
    (etc)
FROM table

Upvotes: 1

Rodion
Rodion

Reputation: 886

You can do this by using PIVOT, but your column number must be finite and given like:

PIVOT
    (
        SUM (Indicator) FOR Day IN (  [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
                    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
                    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
    ) AS pvt

Upvotes: 1

Related Questions