Sql-server – How to perform a pivot on multiple columns

pivotsql server

I have a list of data that includes values and the time those values were recorded. I would like to pivot this data so that i have as columns the individual types of observations and the date/time that they were made.

Here's SQL to create some sample data:

DECLARE @Source TABLE (
  id smallint identity primary key,
  grp varchar(3) not null,
  k varchar(10) not null,
  ts datetime not null,
  val varchar(25)
);
INSERT INTO @Source (grp, k, ts, val) VALUES
('HTZ', 'color', '2014-11-19 14:22:57.633', 'Orange'),
('HTZ', 'shape', '2014-07-23 18:31:51.797', 'Cone'),
('HTZ', 'subst', '2014-01-12 04:09:15.300', 'Canvas'),
('KTU', 'color', '2014-03-08 09:16:47.450', 'Yellow'),
('KTU', 'shape', '2014-01-27 03:51:11.810', 'Octagon'),
('KTU', 'subst', '2014-06-01 20:43:22.577', 'Granite'),
('QXR', 'color', '2014-08-13 05:18:21.917', 'Pink'),
('QXR', 'shape', '2014-09-02 12:27:13.233', 'Ovoid'),
('QXR', 'subst', '2014-08-21 16:52:32.067', 'Steel'),
('WOR', 'color', '2014-09-07 01:43:55.723', 'Teal'),
('WOR', 'shape', '2014-03-14 22:04:23.680', 'Square'),
('WOR', 'subst', '2014-04-06 04:36:28.167', 'Plastic');

This is what I'm trying to achieve:

grp  color    shape    subst    color_ts                shape_ts                subst_ts
---- -------- -------- -------- ----------------------- ----------------------- -----------------------
HTZ  Orange   Cone     Canvas   2014-11-19 14:22:57.633 2014-07-23 18:31:51.797 2014-01-12 04:09:15.300
KTU  Yellow   Octagon  Granite  2014-03-08 09:16:47.450 2014-01-27 03:51:11.810 2014-06-01 20:43:22.577
QXR  Pink     Ovoid    Steel    2014-08-13 05:18:21.917 2014-09-02 12:27:13.233 2014-08-21 16:52:32.067
WOR  Teal     Square   Plastic  2014-09-07 01:43:55.723 2014-03-14 22:04:23.680 2014-04-06 04:36:28.167

I've tried the following query:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source
)
SELECT *
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

However, I end up with one row per grp+k, instead of one per grp. What am I missing?

Best Answer

So here's what I did. I put what you had in a temp table and then grouped the results like so:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source

)
SELECT *
INTO #tmp
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

Select 
    grp
    , max(color) as color
    , max(shape) as shape
    , max(subst) as subst
    , max(color_ts) as color_ts
    , max(shape_ts) as shape_ts
    , max(subst_ts) as subst_ts
from #tmp
group by grp

It's not pretty, but it worked.

Related Solutions

Pivot rows into multiple columns

There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.

Aggregate /Case version:

select personid,
  max(case when optionid = 'A' then 1 else 0 end) OptionA,
  max(case when optionid = 'B' then 1 else 0 end) OptionB,
  max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;

See SQL Fiddle with Demo

Static Pivot:

select *
from
(
  select personid, optionid
  from PersonOptions
) src
pivot
(
  count(optionid)
  for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid

See SQL Fiddle with Demo

Dynamic Version:

The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select personid ';

    begin
        for x in (select distinct OptionID from PersonOptions order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from PersonOptions group by personid order by personid';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you return the results, you will use:

variable x refcursor
exec dynamic_pivot_po(:x)
print x

The results are the same with all versions:

| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
|        1 |       1 |       1 |       0 |
|        2 |       0 |       0 |       1 |
|        3 |       0 |       1 |       0 |
|        4 |       1 |       0 |       1 |

Sql-server – QUERY – pivot multiple columns, variable number of rows

The problem here seems to me to be largely more of a scoping issue - you are likely having difficulty solving this problem on account of the requirements not being defined well enough. With the description and sample data provided, there are at least three partial solutions, none of which may be applicable to your particular use cases. With the test data set up as follows,

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'Recipe'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.Recipe;
    CREATE TABLE dbo.Recipe
    (
        Recipe          INTEGER NOT NULL,
        VersionID       INTEGER NOT NULL,
        Ingredient      VARCHAR( 8 ) NOT NULL,
        Percentage      DECIMAL( 5, 2 )
    );

    INSERT INTO dbo.Recipe ( Recipe, VersionID, Ingredient, Percentage )
                SELECT  4000, 100, 'Ing_1', 23.0
    UNION ALL   SELECT  4000, 100, 'Ing_100', 0.1
    UNION ALL   SELECT  4000, 200, 'Ing_1', 20.0
    UNION ALL   SELECT  4000, 200, 'Ing_100', 0.7
    UNION ALL   SELECT  4000, 300, 'Ing_1', 22.3
    UNION ALL   SELECT  4000, 300, 'Ing_100', 0.9
    UNION ALL   SELECT  4001, 900, 'Ing_1', 8.3
    UNION ALL   SELECT  4001, 900, 'Ing_100', 72.4
    UNION ALL   SELECT  4001, 901, 'Ing_1', 9.3
    UNION ALL   SELECT  4001, 901, 'Ing_100', 70.5
    UNION ALL   SELECT  5012, 871, 'Ing_1', 45.1
    UNION ALL   SELECT  5012, 871, 'Ing_100', 0.9
    UNION ALL   SELECT  5012, 877, 'Ing_1', 47.2
    UNION ALL   SELECT  5012, 877, 'Ing_100', 0.8
    UNION ALL   SELECT  5012, 879, 'Ing_1', 46.6
    UNION ALL   SELECT  5012, 879, 'Ing_100', 0.9
    UNION ALL   SELECT  5012, 880, 'Ing_1', 43.6
    UNION ALL   SELECT  5012, 880, 'Ing_100', 1.2;

    ALTER TABLE dbo.Recipe
    ADD CONSTRAINT PK__Recipe
        PRIMARY KEY CLUSTERED ( Recipe, VersionID, Ingredient );

    CREATE NONCLUSTERED INDEX IX__Recipe__Recipe__VersionID
        ON  dbo.Recipe ( Recipe, VersionID )
    INCLUDE ( Percentage );
END;
GO

we can use our new table to explore some possible solutions. Expanding on the sample output, we add the next recipe into the result set to illustrate the difficulty with the question.

RECIPE --- INGREDIENT_V100 --- PERCENTAGE_V100 --- INGREDIENT_V200 --- INGREDIENT_V200 
4000       Ing_1               23,0                Ing_1               20,0
4000       Ing_100              0,1                Ing_100              0,7
4001       Ing_1                8,3                Ing_1                9,3
4001       Ing_100             72,4                Ing_100             70,5

Columns %_V100 and %_V200 make perfect sense in the case of the 4000 recipe, but quickly lose their meaning as additional recipes are added. The 4001 recipe would need new and separate columns to properly label the data by version, but since the version numbers differ for each and every recipe, that path leads us to a very sparse result set which would be downright annoying to use, or we must alias the columns, losing the version number data.

Solution 1:

Starting with what I feel is the absolute worst way of going about this, let's look at the sparse result set. For the sample data, we would be attempting to generate a query which would look like something along the following lines:

SELECT  p.Recipe,
        [Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100], 
        [Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200], 
        [Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300], 
        [Ingredient_v871] = CASE WHEN p.[871] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v871] = p.[871], 
        [Ingredient_v877] = CASE WHEN p.[877] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v877] = p.[877], 
        [Ingredient_v879] = CASE WHEN p.[879] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v879] = p.[879], 
        [Ingredient_v880] = CASE WHEN p.[880] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v880] = p.[880], 
        [Ingredient_v900] = CASE WHEN p.[900] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v900] = p.[900], 
        [Ingredient_v901] = CASE WHEN p.[901] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v901] = p.[901]
FROM (  SELECT  r.Recipe, 
                r.VersionID, 
                r.Ingredient,
                r.Percentage 
        FROM    dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
        FOR s.VersionID IN ( [100], [200], [300], [871], [877], [879], [880], [900], [901] ) ) p
ORDER BY p.Recipe;

On account of the variable number of versions, we can use some dynamic SQL to generate and execute the query.

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX );

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID 
                FROM    dbo.Recipe r ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
                    + ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
                    + ' ELSE p.[Ingredient] END, ' 
                    + '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.[' 
                    + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], ' 
        FROM (  SELECT  DISTINCT r.VersionID 
                FROM    dbo.Recipe r ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  r.Recipe, 
                        r.VersionID, 
                        r.Ingredient,
                        r.Percentage 
                FROM    dbo.Recipe r ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.VersionID IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO

This result set plainly sucks. Here's an SQL Fiddle which displays the results, take a peek and let's move on.

Solution 2:

With the sparse result set proving to be next to useless, we can accept losing the version numbers of the recipes and simply order them in ascending version number. For the purpose of the example we'll alias alphabetically, so that versions 100, 200 and 300 of recipe 4000 will receive A, B and C designations, whereas versions 900 and 901 will receive just A and B. The query we would like to generate for this should look somewhat like the following:

SELECT  p.Recipe, 
        [Ingredient_vA] = p.[Ingredient], [Percentage_vA] = ISNULL( p.[Percentage_vA], 0 ),
        [Ingredient_vB] = p.[Ingredient], [Percentage_vB] = ISNULL( p.[Percentage_vB], 0 ),
        [Ingredient_vC] = p.[Ingredient], [Percentage_vC] = ISNULL( p.[Percentage_vC], 0 ),
        [Ingredient_vD] = p.[Ingredient], [Percentage_vD] = ISNULL( p.[Percentage_vD], 0 )
FROM (  SELECT  Lvl = 'Percentage_v' + CHAR( 64 + 
                    DENSE_RANK() OVER ( 
                        PARTITION BY r.Recipe
                        ORDER BY r.VersionID ) ), 
                r.Recipe, 
                r.Ingredient,
                r.Percentage 
        FROM    dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
        FOR s.Lvl IN ( [Percentage_vA], [Percentage_vB], [Percentage_vC], [Percentage_vD] ) ) p
ORDER BY p.Recipe;

Similarly to solution one, dynamic SQL can be leveraged to accomplish this.

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX );

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Percentage_v' + CHAR( 64 + a.Lvl ) + '], '
        FROM (  SELECT  DISTINCT Lvl = DENSE_RANK() 
                            OVER (  PARTITION BY r.Recipe
                                    ORDER BY r.VersionID )
                FROM    dbo.Recipe r ) a
        ORDER BY a.Lvl
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Col, LEN( b.Col ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CHAR( 64 + a.Lvl ) + '] = p.[Ingredient], '
                    + '[Percentage_v' + CHAR( 64 + a.Lvl ) + '] = ISNULL( p.[Percentage_v'
                    + CHAR( 64 + a.Lvl ) + '], 0 ),'
        FROM (  SELECT  DISTINCT Lvl = DENSE_RANK() 
                            OVER (  PARTITION BY r.Recipe
                                    ORDER BY r.VersionID )
                FROM    dbo.Recipe r ) a
        ORDER BY a.Lvl
        FOR XML PATH ( '' ) ) b ( Col );

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  Lvl = ''Percentage_v'' + CHAR( 64 + 
                            DENSE_RANK() OVER ( 
                                PARTITION BY r.Recipe
                                ORDER BY r.VersionID ) ), 
                        r.Recipe, 
                        r.Ingredient,
                        r.Percentage 
                FROM    dbo.Recipe r ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.Lvl IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO

This ends up in a much prettier result set, as can be seen in this SQL Fiddle, despite the loss of the specific version numbers of each recipe.

Solution 3:

If the loss of the version numbers can not be tolerated, a hybrid approach can be implemented, though the results from each call would be limited to only a single recipe. In effect, our goal SQL would be similar to the first solution, but with an expressly defined Recipe number.

SELECT  p.Recipe, 
        [Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100], 
        [Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200], 
        [Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300]
FROM (  SELECT  r.Recipe, 
                r.VersionID, 
                r.Ingredient,
                r.Percentage 
        FROM    dbo.Recipe r
        WHERE   r.Recipe = @Recipe ) s
PIVOT ( MAX( s.Percentage )
        FOR s.VersionID IN ( [100], [200], [300] ) ) p
ORDER BY p.Recipe;

Generation could be handled as follows:

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @Param          NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX ),
        @Recipe         INTEGER = 4000;

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID 
                FROM    dbo.Recipe r
                WHERE   Recipe = @Recipe ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
                + ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
                + ' ELSE p.[Ingredient] END, ' 
                + '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.[' 
                    + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], ' 
        FROM (  SELECT  DISTINCT r.VersionID 
                FROM    dbo.Recipe r
                WHERE   Recipe = @Recipe ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SET @Param = N'@Recipe  INTEGER';

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  r.Recipe, 
                        r.VersionID, 
                        r.Ingredient,
                        r.Percentage 
                FROM    dbo.Recipe r
                WHERE   r.Recipe = @Recipe ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.VersionID IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL, @param = @Param, @Recipe = @Recipe;
GO

The results could then be accessed per recipe, as shown in this SQL Fiddle or this one.