SQL Server – Using PIVOT to Convert Multiple Rows and Columns

pivotsql serversql server 2014ssmsunpivot

So my current query output is as follows:

I would like to consolidate all of these rows to just one long row like so:

MyID    Customer  relc_rate  relc_consumption   relc_daysofservice   relc_unitmeasure   relc_charge   relc_chargedate
-----   --------  ---------  ----------------   ------------------   ----------------   -----------   ---------------
10016    112992    110W            753                  31                 KWH

and Pretty much repeat that for RGAS, RWTR, etc all across one row. Yes I understand that will be about over 50 columns if I consolidate all the service types into its own column.

Now I was able to get started with getting the service number into the "Type" using this Pivot query. However, I got stuck after that trying to figure out how to get each service type, along with the rate, etc into it's own column.

select *
from 
( 
 SELECT [Premise]
    , [Premise Address] premise_address
    , [Customer] as customer_no
    , [Service Type]
    , [Service Number]

 FROM UTIL_MAY2019
) premise_table
pivot 
(
  sum([Service number])
  for [Service Type] in ([RELC], [RWTR], [RSWR], [RGAR], [STRM], [DELC], [SLCM], [CGAS], [CWTR], [CSWR], [CGAR])
) piv_premise

Here's the DDL for testing:

CREATE TABLE supercharge (
MyID int
 , customer int 
 , Servicetype varchar(10)
 , servicerate varchar(10)
 , serviceNumber int
 , consumption int
 , daysofservice int
 , unitofmeasure varchar(10)
 , chargeAmount double
 , chargeDate datetime  
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate) 

VALUES 
(10016, 112992, RELC, '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'), 
(10016, 112992, RGAS, '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'), 
(10016, 112992, RWTR, '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'), 
(10016, 112992, RSWR, '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'), 
(10016, 112992, RGAR, '', 500, null, null, '',32.12, '2019-05-08'), 
(10016, 112992, STRM, '', 700, null, null, '',2.38, '2019-05-08'), 
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'), 
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

Best Answer

Well, this solution is not particularly sophisticated (it doesn't use PIVOT), but it would solve your problem. There are probably smarter ways to address the problem, but here is one method.

The solution selects from a derived unpivoted table (note comment about unpivot original data). It groups by MyId and customer and uses CASE expressions to derive the individual column names and values. My example only show two of the servicetype columns broken out into separate columns, but hopefully you'll see the pattern. (I'm not sure how you handle the rows with blank servicetype).

--demo setup
DROP TABLE IF EXISTS supercharge
GO
CREATE TABLE supercharge (
MyID int
 , customer int 
 , Servicetype varchar(10)
 , servicerate varchar(10)
 , serviceNumber int
 , consumption int
 , daysofservice int
 , unitofmeasure varchar(10)
 , chargeAmount decimal(11,2)
 , chargeDate datetime  
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate) 

VALUES 
(10016, 112992, 'RELC', '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'), 
(10016, 112992, 'RGAS', '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'), 
(10016, 112992, 'RWTR', '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'), 
(10016, 112992, 'RSWR', '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'), 
(10016, 112992, 'RGAR', '', 500, null, null, '',32.12, '2019-05-08'), 
(10016, 112992, 'STRM', '', 700, null, null, '',2.38, '2019-05-08'), 
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'), 
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

--solution
SELECT MyID
    ,customer
--RELC columns
    ,max(CASE 
            WHEN Servicetype = 'RELC'
                AND col = 'rate'
                THEN value
            END) AS Relc_Rate
    ,max(CASE 
            WHEN Servicetype = 'RELC'
                AND col = 'consumption'
                THEN value
            END) AS Relc_Consumption
    ,max(CASE 
            WHEN Servicetype = 'RELC'
                AND col = 'daysofservice'
                THEN value
            END) AS Relc_DaysOfService
    ,max(CASE 
            WHEN Servicetype = 'RELC'
                AND col = 'unitofmeasure'
                THEN value
            END) AS Relc_UnitOfMeasure
    ,max(CASE 
            WHEN Servicetype = 'RELC'
                AND col = 'chargeamount'
                THEN value
            END) AS Relc_Charge
    ,max(CASE 
            WHEN Servicetype = 'RELC'
                AND col = 'chargedate'
                THEN value
            END) AS Relc_ChargeDate
--RGAS columns
    ,max(CASE 
            WHEN Servicetype = 'RGAS'
                AND col = 'rate'
                THEN value
            END) AS Rgas_Rate
    ,max(CASE 
            WHEN Servicetype = 'RGAS'
                AND col = 'consumption'
                THEN value
            END) AS Rgas_Consumption
    ,max(CASE 
            WHEN Servicetype = 'RGAS'
                AND col = 'daysofservice'
                THEN value
            END) AS Rgas_DaysOfService
    ,max(CASE 
            WHEN Servicetype = 'RGAS'
                AND col = 'unitofmeasure'
                THEN value
            END) AS Rgas_UnitOfMeasure
    ,max(CASE 
            WHEN Servicetype = 'RGAS'
                AND col = 'chargeamount'
                THEN value
            END) AS Rgas_Charge
    ,max(CASE 
            WHEN Servicetype = 'RGAS'
                AND col = 'chargedate'
                THEN value
            END) AS Rgas_ChargeDate

FROM
--unpivot original data
(
select MyID,customer,Servicetype, col, value
from supercharge
cross apply
(
  select 'rate', cast(servicerate as varchar(10)) union all
  select 'consumption', cast(consumption as varchar(10)) union all
  select 'daysofservice', cast(daysofservice as varchar(10)) union all
  select 'unitofmeasure', cast(unitofmeasure as varchar(10)) union all
  select 'chargeamount', cast(chargeAmount as varchar(10)) union all
  select 'chargeDate', convert(varchar(10), chargeDate,121)
) c(col, value) 
) d
GROUP BY MyID
    ,customer;

| MyID  | customer | Relc_Rate | Relc_Consumption | Relc_DaysOfService | Relc_UnitOfMeasure | Relc_Charge | Relc_ChargeDate | Rgas_Rate | Rgas_Consumption | Rgas_DaysOfService | Rgas_UnitOfMeasure | Rgas_Charge | Rgas_ChargeDate |
|-------|----------|-----------|------------------|--------------------|--------------------|-------------|-----------------|-----------|------------------|--------------------|--------------------|-------------|-----------------|
| 10016 | 112992   | 110W      | 753              | 31                 | KWH                | 99.92       | 2019-05-08      | 120       | 0                | 31                 | CCF                | 5.31        | 2019-05-08      |

Related Solutions

How to Pivot Rows into Multiple Columns in Oracle

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 2008 R2 – How to Pivot Multiple Columns with 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.