pivot table how to create the following

Question

i have table like this

DisposalID  ReportID

242            84
243            84

i want to see it following way

DisposalID  DisposalID     ReportID

242              243               84

i tried using pivot table but could not achieve that

select 
  *
from
(
SELECT [DisposalID]
,[ReportID]

FROM [ClearData_Test].[dbo].[DisposalConsolidatedView] WHERE [ReportID]=84
) DataTable
PIVOT
(
  Min(disposalid)
  FOR reportid
  IN ([84])
) PivotTable

its only gives me min or max. Any help would be greatly appriciated.

score 0 · Accepted Answer · answered Nov 05 '13 at 11:35

Since PIVOT requires aggregation, your current query will only return the min/max value of each DisposalId. In order to get the result that you want you have to create column that will be used as your new column headers.

I would use row_number() and partition the data by your ReportId. The subquery that you are using would be altered slightly to the following:

SELECT [DisposalID]
  ,[ReportID]
  , 'DisposalId'
      +cast(row_number() over(partition by reportId
                              order by disposalid) as varchar(10)) seq
FROM [dbo].[DisposalConsolidatedView] 
WHERE [ReportID]=84

See SQL Fiddle with Demo. This is going to create a column with the values DisposalId1 and DisposalId2 - these new values will be your new column headers and then you will apply the aggregate to your existing DisposalId column.

The final syntax will be:

select DisposalId1, DisposalId2, ReportId
from
(
  SELECT [DisposalID]
    ,[ReportID]
    , 'DisposalId'
        +cast(row_number() over(partition by reportId
                                order by disposalid) as varchar(10)) seq
  FROM [dbo].[DisposalConsolidatedView] 
  WHERE [ReportID]=84
  ) DataTable
PIVOT
(
  Min(disposalid)
  FOR seq IN (DisposalId1, DisposalId2)
) PivotTable;

See SQL Fiddle with Demo. This will give a result:

| DISPOSALID1 | DISPOSALID2 | REPORTID |
|-------------|-------------|----------|
|         242 |         243 |       84 |

perfect worked like a charm....One more little question what if there are more then 2 disposal IDs? They can have potentially upto 20. — user2954221, Nov 05 '13 at 14:33
@user2954221 You will either have to hard-code the PIVOT `IN` clause to include up to 20 column names or use dynamic SQL to create the final result. Here is an answer of mine with a dynamic sql example -- http://stackoverflow.com/questions/19709206/pivot-table-issue-no-identifier-in-original-table-or-enumeration/19709603#19709603 — Taryn, Nov 05 '13 at 14:35

score 0 · Answer 2 · edited Dec 17 '16 at 12:20

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('DisposalID'+cast(seq as varchar(10))) 
                from
                (
                  select row_number() over(partition by [reportId] 
                                           order by disposalid) seq
                  from [ClearData_Test].[dbo].[DisposalConsolidatedView]
                ) d
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT [ReportID],' + @cols + ' 
        from 
        (
          SELECT [ReportID], disposalid,
            ''disposalid''+
              cast(row_number() over(partition by  [reportId] 
                                     order by disposalid) as varchar(10)) seq
          FROM  [ClearData_Test].[dbo].[DisposalConsolidatedView]
          where[ReportID]=''84''
        ) x
        pivot 
        (
           Min(disposalid)
            for seq in (' + @cols + ')
        ) p '

execute sp_executesql @query;

That was the solution I was looking for.

pivot table how to create the following

2 Answers2