Pivoting help needed

Question

Please help me with this. I am totally stuck. I have coders block or something.

I have the following table

ID     Name        Cost     Included
----   ----------  -------  ----------
1      Package1    10.00    Yes
2      Package2    20.00    No
3      Package3    20.00    Yes

I would like to crosstab this information, to display like the following example,there will be more columns in the table.

Type        Package1     Package2       Package3
-----       ------------ -----------    ----------
Name        Package1     Package2       Package3
Cost        10.00        20.00          30.00
Included    Yes          No             Yes

Answer 1

It seems to me that you are trying to build a product comparison list. If this is true, you might unpivot the table first and then join individual records together.

The 'transponded' part unpivots the columns. All columns must be of compatible types or converted to one. I choose varchar(100). transponded returns table with three columns, ID from ProductInfo, Type as column name and Value as value of corresponding column.

Select part joins together info on as many product as demanded by adding another left join transponded tn on t1.Type = tnType and tn.ID = @parametern. This part seems as a hassle, but when I tried to do this part with pivot I failed to get column in proper order - pivot sorted names in Type. It would however demand dynamic sql generation. This solution is fixed providing that you add enough joins for maximum products you wish to compare at once. I belive it would not be over 5.

=1, =2 and =3 should be replaced by parameters. The query should be hosted in stored procedure.

; with transponded as 
(
  select ID, Type, Value
    from 
    (
      select ID, 
             Name, 
             cast (Cost as varchar(100)) Cost, 
             cast (case when Included = 1 then 'Yes' else 'No' end as varchar(100)) Included
        from ProductInfo
    ) p
      unpivot (Value for Type in (Name, Cost, Included) ) a
)
select t1.Type, 
       t1.Value Product1, 
       t2.Value Product2, 
       t3.Value Product3
  from transponded t1
  left join transponded t2
         on t1.Type = t2.Type
        and t2.id = 2
   left join transponded t3
     on t1.Type = t3.Type
    and t3.id = 3
  where t1.id = 1

In short, transpond one record at time and join to another transponded record by Type column.

Oh, and here is a Sql Fiddle playground.

Answer 2

If number of packages is not static there is no option for you I think. PIVOT clause can produce only static/defined number of columns.

You may do some table-to-table rewriting using multiple statements - but still you have to face with static number of columns.

But you may set it to for example to 10 and then display up to 10 packages, having NULL-s in rest of columns if there are less packages.

You may also use dynamic SQL to have dynamic number of columns - but it will be a headache.

If you're going to export this data to Excel - do not pivot it at SQL - do a transposition in Excel (it's under "paste special").

Answer 3

Basically what i have at this stage is the following.

        SELECT [Type],
            MAX(Beginner) AS [Beginner],
            MAX(Intermediate) AS [Intermediate],
            MAX(Advanced) AS [Advanced]
    FROM
    (
        SELECT 
            'Name' AS TYPE,
            CASE WHEN Name='Beginner' THEN Name END AS [Beginner],
            CASE WHEN Name='Intermediate' THEN Name END AS [Intermediate],
            CASE WHEN Name='Advanced' THEN Name END AS [Advanced]
        FROM Administration.Package
        UNION ALL
        SELECT 
            'Price' AS TYPE,
            CASE WHEN Name='Beginner' THEN CAST(Price AS VARCHAR) END AS [Beginner],
            CASE WHEN Name='Intermediate' THEN CAST(Price AS VARCHAR) END AS [Intermediate],
            CASE WHEN Name='Advanced' THEN CAST(Price AS VARCHAR) END AS [Advanced]
        FROM Administration.Package
    )A
    GROUP BY [Type]

But it does not feel right to have the union for each and every column.

Answer 4

There is no easy way to do this, as the pivot will need to be aggregated by column. Given that adding columns to the input table would cause a maintenance issue where these values will not be presented to the output until the code is changed wherever it is used, I'd say you're probably best doing it once with a stored procedure, which will dynamically generate the output you're looking for based on the schema of the input table.

I have demonstrated how this can be done, using the data you have supplied. This data is stored in a temp table (not #temp, because the stored proc won't work with temporary tables), populated thus:

CREATE TABLE temp (
    _key int,
    package_name varchar(50),
    cost float,
    included bit
)

INSERT INTO temp VALUES(1,'Package1',    10.00,    1)
INSERT INTO temp VALUES(2,'Package2',    20.00,    0)
INSERT INTO temp VALUES(3,'Package3',    20.00,    1)

The stored procedure retrieves a list of values based on the @pivot_field parameter, and uses these values as a column list to be inserted after the "Type" field. It then unions the pivot field and all other fields together to generate the rows, pivoting one column at a time. The procedure is as follows:

CREATE PROCEDURE usp_get_pivot (@table_name nvarchar(255), @pivot_field nvarchar(255)) AS
BEGIN
    CREATE TABLE #temp (val nvarchar(max))

    DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX), @col NVARCHAR(255)

    SET @sql = 'SELECT DISTINCT ' + @pivot_field + ' FROM ' + @table_name
    INSERT INTO #temp EXEC sp_executesql @sql;
    SET @cols = (SELECT '[' + val + '],' FROM #temp FOR XML PATH(''))
    SET @cols = SUBSTRING(@cols, 1, LEN(@cols)-1)

    SET @SQL = N'SELECT ''' + @pivot_field + ''' as [type], *
    FROM (SELECT ' + @pivot_field + ', ' + @pivot_field + ' as ' + @pivot_field + '1 FROM ' + @table_name + ') AS source_table
    PIVOT (max(' + @pivot_field + '1) FOR ' + @pivot_field + ' IN (' + @cols + ')) AS pivot_table'

    DECLARE csr CURSOR FOR 
        SELECT c.name FROM sys.columns c, sys.objects o 
        WHERE c.object_id = o.object_id AND o.name = @table_name
        AND c.name <> @pivot_field
        ORDER BY column_id

    OPEN csr
    FETCH NEXT FROM csr INTO @col
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = @sql + ' UNION ALL
        SELECT ''' + @col + ''' as [type], *
        FROM (SELECT ' + @pivot_field + ', CAST(' + @col + ' AS VARCHAR) AS ' + @col + ' FROM ' + @table_name + ') AS source_table
        PIVOT (max(' + @col + ') FOR ' + @pivot_field + ' IN (' + @cols + ')) AS pivot_table'
        FETCH NEXT FROM csr INTO @col
    END

    CLOSE csr
    DEALLOCATE csr
    DROP TABLE #temp
    EXEC sp_executesql @sql
END

You should be able to simply copy and paste the procedure into management studio, create the data is shown above and execute the procedure with:

EXEC usp_get_pivot 'temp', 'package_name'