Dynamic Pivot Needed with Row_Number()

Dynamic Pivot Needed with Row_Number()

with using a cte, with row_number you can achieve the result:

Your Schema:

create table your_table([Client ID] int ,Value varchar(50));
insert into your_table values
(12345,            'Did Not Meet'),
(12345,            'Did Not Meet'),
(12345,            'Partially Met'),
(12346,            'Partially Met'),
(12346,            'Partially Met'),
(12346,            'Partially Met'),
(12347,            'Partially Met'),
(12347,            'Partially Met'),
(12347,            'Did Not Meet'),
(12347,            'Met');

The query:

with cte as
(
 select [Client ID] ci,value,
        row_number() over(partition by [Client ID] order by value) as rn
 from your_table
)
select distinct ci as [Client ID],
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=1) value1,
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=2) value2,
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=3) value3,
       (select ct.value from cte ct where ct.ci=cte.ci and ct.rn=4) value4
from cte

The Result:

Client ID   value1          value2          value3          value4
12345       Did Not Meet    Did Not Meet    Partially Met   (null)
12346       Partially Met   Partially Met   Partially Met   (null)
12347       Did Not Meet    Met Partially   Met Partially    Met

How to properly create a Dynamic Pivot Function in SQL?

You need to PIVOT off a derived column using row_number()

Example

Declare @SQL varchar(max) = stuff( ( Select Distinct concat(',[NetAmount',row_number() over (partition by [Bank Name],[Account] order by [TranID]),']' ) 
                                      From #TEMP For XML Path('') ),1,1,'')

Set @SQL = '
Select *
 From (
        Select [Account]
              ,[Bank Name]
              ,Item  = concat(''NetAmount'',row_number() over (partition by [Bank Name],[Account] order by [TranID]) )
              ,Value = [NetAmount]
         from #TEMP
      ) src
 Pivot ( max( [Value] ) for Item in ('+ @SQL   +') ) pvt
'

Exec(@SQL)

Results

Account Bank Name       NetAmount1  NetAmount2
A       StormWindBank   23.0$       00.0$
B       StormWindBank   14.0$       12.0$

SQL rotate rows to columns...dynamic number of rows

If you have an unknown number of values, then you can use a PIVOT with dynamic SQL:

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

select @cols = STUFF((SELECT distinct ',' 
                        + QUOTENAME('Measurement_' + cast(rn as varchar(10))) 
                    from temptable
                    cross apply
                    (
                      select row_number() over(partition by measure_id order by measurement) rn
                      from temptable
                    ) x
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT measure_id, ' + @cols + ' from 
             (
                select measure_id, measurement,
                  ''Measurement_''
                    + cast(row_number() over(partition by measure_id order by measurement) as varchar(10)) val
                from temptable
            ) x
            pivot 
            (
                max(measurement)
                for val in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle With Demo

If you have a known number of values, then you can hard-code the values, similar to this:

SELECT measure_id, [Measurement_1], [Measurement_2], 
               [Measurement_3], [Measurement_4]
from 
(
  select measure_id, measurement,
    'Measurement_'
     + cast(row_number() over(partition by measure_id order by measurement) as varchar(10)) val
  from temptable
) x
pivot 
(
   max(measurement)
   for val in ([Measurement_1], [Measurement_2], 
               [Measurement_3], [Measurement_4])
) p 

See SQL Fiddle With Demo

Both queries will produce the same results:

MEASURE_ID | MEASUREMENT_1 | MEASUREMENT_2 | MEASUREMENT_3 | MEASUREMENT_4
==========================================================================
1          | 2.3           | 3.3           | 3.4           | (null)
2          | 2.3           | 3             | 4             | 4.5

Dynamic pivot table query with grouping in T-SQL

Based on comments, it seems you are looking for some Dynamic SQL

Example

Declare @SQL varchar(max) = '
Select *
 From  (
        Select ID
              ,Item = row_number() over (partition by ID order by Label)
              ,Value = concat(Label,'' ('',Tag1,'','',Tag2,'','',Tag3,'','',Tag4,'')'')
         From YourTable
       ) src
 Pivot ( max(Value) for Item in ( ' + stuff(( Select distinct ','+quotename(row_number() over (partition by ID order by Label)) 
                                              From  YourTable
                                               For XML Path('')),1,1,'') +')) pvt
'

Exec(@SQL)

Results

---

Related Topics