I'm having trouble figuring out how to return a query result where I have row values that I want to turn into columns. In short, here is an example of my current schema in SQL Server 2008: And here is an example of what I would like the query result to look like: Here is the SQLFiddle.com to play with - http://sqlfiddle.com/#!6/6b394/1/0 Some helpful notes about the schema: The table will always contain two rows for each day One row for Apples, and one row for Oranges I am trying to combine each pair of two rows into one row To do that I need to convert the row values into their own columns, but only the values for [NumOffered], [NumTaken], [NumAbandoned], [NumSpoiled] - NOT every column for each of these two rows needs to be duplicated, as you can see from the example As you can see, from the example desired end result image, the two rows are combined, and you can see each value has its own column with a relevant name. I've seen several examples of how this is possible, but not quite applicable for my purposes. I've seen examples using Grouping and Case methods. I've seen many uses of PIVOT, and even some custom function creation in SQL. I'm not sure which is the best approach for me. Can I get some insight on this?

sqlsql-serversql-server-2008

Armin

Reputation: 1756

SQL query for returning row values as columns - SQL Server 2008

I'm having trouble figuring out how to return a query result where I have row values that I want to turn into columns.

In short, here is an example of my current schema in SQL Server 2008:

And here is an example of what I would like the query result to look like:

Here is the SQLFiddle.com to play with - http://sqlfiddle.com/#!6/6b394/1/0

Some helpful notes about the schema:

The table will always contain two rows for each day
One row for Apples, and one row for Oranges
I am trying to combine each pair of two rows into one row
To do that I need to convert the row values into their own columns, but only the values for [NumOffered], [NumTaken], [NumAbandoned], [NumSpoiled] - NOT every column for each of these two rows needs to be duplicated, as you can see from the example

As you can see, from the example desired end result image, the two rows are combined, and you can see each value has its own column with a relevant name.

I've seen several examples of how this is possible, but not quite applicable for my purposes. I've seen examples using Grouping and Case methods. I've seen many uses of PIVOT, and even some custom function creation in SQL. I'm not sure which is the best approach for me. Can I get some insight on this?

Upvotes: 3

Answers (4)

Taryn

Reputation: 247620

There are many different ways that you can get the result. Multiple JOINs, unpivot/pivot or CASE with aggregate.

They all have pros and cons, so you'll need to decide what will work best for your situation.

Multiple Joins - now you've stated that you will always have 2 rows for each day - one for apple and orange. When joining on the table multiple times your need some sort of column to join on. It appears the column is timestamp but what happens if you have a day that you only get one row. Then the INNER JOIN solution provided by @Becuzz won't work because it will only return the rows with both entries per day. LeYou could use multiple JOINs using a FULL JOIN which will return the data even if there is only one entry per day:

select 
  [Timestamp] = Coalesce(a.Timestamp, o.Timestamp),
  ApplesNumOffered = a.[NumOffered],
  ApplesNumTaken = a.[NumTaken],
  ApplesNumAbandoned = a.[NumAbandoned],
  ApplesNumSpoiled = a.[NumSpoiled],
  OrangesNumOffered = o.[NumOffered],
  OrangesNumTaken = o.[NumTaken],
  OrangesNumAbandoned = o.[NumAbandoned],
  OrangesNumSpoiled = o.[NumSpoiled]
from
(
  select timestamp, numoffered, NumTaken, numabandoned, numspoiled
  from myTable
  where FruitType = 'Apple'
) a
full join
(
  select timestamp, numoffered, NumTaken, numabandoned, numspoiled
  from myTable
  where FruitType = 'Orange'
) o 
  on a.Timestamp = o.Timestamp
order by [timestamp];

See SQL Fiddle with Demo. Another issue with multiple joins is what if you have more than 2 values, you'll need an additional join for each value.

If you have a limited number of values then I'd suggest using an aggregate function and a CASE expression to get the result:

SELECT 
  [timestamp],
  sum(case when FruitType = 'Apple' then NumOffered else 0 end) AppleNumOffered,
  sum(case when FruitType = 'Apple' then NumTaken else 0 end) AppleNumTaken,
  sum(case when FruitType = 'Apple' then NumAbandoned else 0 end) AppleNumAbandoned,
  sum(case when FruitType = 'Apple' then NumSpoiled else 0 end) AppleNumSpoiled,
  sum(case when FruitType = 'Orange' then NumOffered else 0 end) OrangeNumOffered,
  sum(case when FruitType = 'Orange' then NumTaken else 0 end) OrangeNumTaken,
  sum(case when FruitType = 'Orange' then NumAbandoned else 0 end) OrangeNumAbandoned,
  sum(case when FruitType = 'Orange' then NumSpoiled else 0 end) OrangeNumSpoiled
FROM myTable
group by [timestamp];

See SQL Fiddle with Demo. Or even using PIVOT/UNPIVOT like @M.Ali has. The problem with these are what if you have unknown values - meaning more than just Apple and Orange. You are left with using dynamic SQL to get the result. Dynamic SQL will create a string of sql that needs to be execute by the engine:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(FruitType + col) 
                    from
                    (
                      select FruitType
                      from myTable
                    ) d
                    cross apply
                    (
                      select 'NumOffered', 0 union all
                      select 'NumTaken', 1 union all
                      select 'NumAbandoned', 2 union all
                      select 'NumSpoiled', 3
                    ) c (col, so)
                    group by FruitType, Col, so
                    order by FruitType, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT TimeStamp,' + @cols + ' 
            from 
            (
               select TimeStamp,
                new_col = FruitType+col, value
               from myTable
               cross apply
               (
                 select ''NumOffered'', NumOffered union all
                 select ''NumTaken'', NumOffered union all
                 select ''NumAbandoned'', NumOffered union all
                 select ''NumSpoiled'', NumOffered
               ) c (col, value)
            ) x
            pivot 
            (
                sum(value)
                for new_col in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo

All versions give the result:

|                 timestamp | AppleNumOffered | AppleNumTaken | AppleNumAbandoned | AppleNumSpoiled | OrangeNumOffered | OrangeNumTaken | OrangeNumAbandoned | OrangeNumSpoiled |
|---------------------------|-----------------|---------------|-------------------|-----------------|------------------|----------------|--------------------|------------------|
| January, 01 2015 00:00:00 |              55 |            12 |                 0 |               0 |               12 |              5 |                  0 |                1 |
| January, 02 2015 00:00:00 |              21 |             6 |                 2 |               1 |               60 |             43 |                  0 |                0 |
| January, 03 2015 00:00:00 |              49 |            17 |                 2 |               1 |              109 |             87 |                 12 |                1 |
| January, 04 2015 00:00:00 |               6 |             4 |                 0 |               0 |               53 |             40 |                  0 |                1 |
| January, 05 2015 00:00:00 |              32 |            14 |                 1 |               0 |               41 |             21 |                  5 |                0 |
| January, 06 2015 00:00:00 |              26 |            24 |                 0 |               1 |               97 |             30 |                 10 |                1 |
| January, 07 2015 00:00:00 |              17 |             9 |                 2 |               0 |               37 |             27 |                  0 |                4 |
| January, 08 2015 00:00:00 |              83 |            80 |                 3 |               0 |              117 |            100 |                  5 |                1 |

Upvotes: 4

Richard Gwilliam

Reputation: 61

M.Ali beat me too it. UNPIVOT and PIVOT are your friends with these types of transformations.

I've used this article multiple times when refreshing my memory with UNPIVOT and PIVOT.

Upvotes: -2

M.Ali

Reputation: 69494

Query

;WITH CTE AS
(SELECT [Timestamp]
      ,FruitType + EventType AS Cols
      ,Qty
from myTable t
  UNPIVOT (Qty FOR EventType IN (NumOffered ,NumTaken
                                 ,NumAbandoned,NumSpoiled))up
)
SELECT * FROM CTE
  PIVOT (SUM(Qty) FOR Cols IN (AppleNumOffered,AppleNumTaken
                              ,AppleNumAbandoned,AppleNumSpoiled
                              ,OrangeNumOffered, OrangeNumTaken
                              ,OrangeNumAbandoned,OrangeNumSpoiled))p

Result

╔═════════════════════════╦═════════════════╦═══════════════╦═══════════════════╦═════════════════╦══════════════════╦════════════════╦════════════════════╦══════════════════╗
║        Timestamp        ║ AppleNumOffered ║ AppleNumTaken ║ AppleNumAbandoned ║ AppleNumSpoiled ║ OrangeNumOffered ║ OrangeNumTaken ║ OrangeNumAbandoned ║ OrangeNumSpoiled ║
╠═════════════════════════╬═════════════════╬═══════════════╬═══════════════════╬═════════════════╬══════════════════╬════════════════╬════════════════════╬══════════════════╣
║ 2015-01-01 00:00:00.000 ║              55 ║            12 ║                 0 ║               0 ║               12 ║              5 ║                  0 ║                1 ║
║ 2015-01-02 00:00:00.000 ║              21 ║             6 ║                 2 ║               1 ║               60 ║             43 ║                  0 ║                0 ║
║ 2015-01-03 00:00:00.000 ║              49 ║            17 ║                 2 ║               1 ║              109 ║             87 ║                 12 ║                1 ║
║ 2015-01-04 00:00:00.000 ║               6 ║             4 ║                 0 ║               0 ║               53 ║             40 ║                  0 ║                1 ║
║ 2015-01-05 00:00:00.000 ║              32 ║            14 ║                 1 ║               0 ║               41 ║             21 ║                  5 ║                0 ║
║ 2015-01-06 00:00:00.000 ║              26 ║            24 ║                 0 ║               1 ║               97 ║             30 ║                 10 ║                1 ║
║ 2015-01-07 00:00:00.000 ║              17 ║             9 ║                 2 ║               0 ║               37 ║             27 ║                  0 ║                4 ║
║ 2015-01-08 00:00:00.000 ║              83 ║            80 ║                 3 ║               0 ║              117 ║            100 ║                  5 ║                1 ║
╚═════════════════════════╩═════════════════╩═══════════════╩═══════════════════╩═════════════════╩══════════════════╩════════════════╩════════════════════╩══════════════════╝

SQL FIDDLE

Upvotes: -1

Becuzz

Reputation: 6866

Given your criteria, joining the two companion rows together and selecting out the appropriate fields seems like the simplest answer. You could go the route of PIVOTs, UNIONs and GROUP BYs, etc., but that seems like overkill.

select apples.Timestamp
        , apples.[NumOffered] as ApplesNumOffered
        , apples.[NumTaken] as ApplesNumTaken
        , apples.[NumAbandoned] as ApplesNumAbandoned
        , apples.[NumSpoiled] as ApplesNumSpoiled
        , oranges.[NumOffered] as OrangesNumOffered
        , oranges.[NumTaken] as OrangesNumTaken
        , oranges.[NumAbandoned] as OrangesNumAbandoned
        , oranges.[NumSpoiled] as OrangesNumSpoiled
from myTable apples
inner join myTable oranges on oranges.Timestamp = apples.Timestamp
where apples.FruitType = 'Apple'
and oranges.FruitType = 'Orange'

Upvotes: 1

SQL query for returning row values as columns - SQL Server 2008

Answers (4)

Related Questions