How to select dynamic number of columns according to rows in sql

Question

I have the following data

Activity         | Indicator          | Impact
------------------------------------------------
Payroll risk     | Indicator A        | Low
Payroll risk     | Indicator B        | Low
Payroll risk     | Indicator C        | Low
Wrong selections | Indicator D        | High
Wrong selections | Indicator E        | High
Fraudulant Cred  | Indicator F        | Medium
Fraudulant Cred  | Indicator G        | Medium

Then this data should be recieved in the following format

Activity         | Indicator   | Indicator   | Indicator   | Impact
--------------------------------------------------------------------
Payroll risk     | Indicator A | Indicator B | Indicator C | Low
Wrong selections | Indicator D | Indicator E |             | High
Fraudulant cred  | Indicator F | Indicator G |             | Medium

What can be the process for the following operation in sql? The number of columns shoild be same as the maximum number of rows for any common record.

Thanks.

Mangal Pardeshi · Accepted Answer · 2015-11-19 07:31:48Z

1

You can do that by using CASE or PIVOT. Here is example of Pivot for your sample data:

SELECT Activity, Impact, [1], [2], [3]

FROM (
SELECT Activity, Indicator, Impact
,ROW_NUMBER()OVER(PARTITION BY Activity ORDER BY Indicator) AS R
FROM @T
) AS M
PIVOT
(
MAX(Indicator)
FOR R IN ([1], [2], [3])
) P

In case you want to do it dynamically you can refer : Dynamic PIVOT in SQL Server

answered Nov 19, 2015 at 7:31

Mangal Pardeshi

3652 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Abhinav Over a year ago

Marked as answer because most of the logic I have found in this. Only modification what I need to make is the number of columns are not fixed. Thanks :)

Mangal Pardeshi Over a year ago

If number of columns are not fixed you check the link i mentioned in the answer, it will help you in writing dynamic pivot.

Mangal Pardeshi Over a year ago

Or in case you know the maximum number of columns then you can just those many numbers to avoid dynamic sql.

Abhishek · Accepted Answer · 2015-11-19 07:34:28Z

1

One way of doing it -

select 
  Activity
, [Indicator] = max(case when RowID = 1 then Indicator end)
, [Indicator] = max(case when RowID = 2 then Indicator end)
, [Indicator] = max(case when RowID = 3 then Indicator end)
,Impact
from (
select 
    Activity
  , Indicator
  , Impact
  , RowID = row_number() over (partition by Impact order by Activity)
  from #PIVOT
)
SourceTable
group by Activity,Impact

answered Nov 19, 2015 at 7:34

Abhishek

2,4901 gold badge24 silver badges35 bronze badges

Comments

BeanFrog · Accepted Answer · 2015-11-19 08:57:16Z

To use the dynamic sql, you need to first find out the max number of columns, and then create a list of those numbers. You then create a string variable with the words of the query, and pop in the column name string you have created. I've called your table RiskImpacts in the example below

Here's how you could find out how many columns you need, and store it in the variable @maxcount

declare @maxcount int;
select @maxcount = max(indcount) 
from (select [Activity],count([Indicator]) as indcount 
      from RiskImpacts group by activity
     ) as countindicators;

Next you need to create a string of column names based on that number. We'll store it in @columnnames:

declare @columnnames nvarchar(max)
set @columnnames = ''
select @columnnames = @columnnames + '[Indicator ' + cast(ROW_NUMBER() over (order by indicator) as nvarchar(50)) + '],' from 
(select top (@maxcount-1) * from RiskImpacts) as a

set @columnnames = @columnnames +'[Indicator ' + cast(@maxcount as nvarchar(50)) + ']'

This gets all except the last one with a comma at the end, and then adds in the last one. @columnnames now looks like this:

[Indicator 1],[Indicator 2],[Indicator 3]

Now we can put this all together in a string to create the query we want to run.

declare @pivotsql nvarchar(max)
set @pivotsql= 'select Activity, ' + @columnnames + ', Impact
    from ( select Activity, Indicator, Impact, ''Indicator '' + cast(ROW_NUMBER()OVER(PARTITION BY Activity ORDER BY Indicator) as nvarchar(50)) as R
            from RiskImpacts
         ) as M
    pivot
        ( max(Indicator) for R in (' + @columnnames + ')
        ) P'

And finally we need to execute that piece of SQL

exec (@pivotsql)

Collectives™ on Stack Overflow

How to select dynamic number of columns according to rows in sql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related