CODEWITHSUNDEEP
sqlsql-server
Ghotekar Rahul
Ghotekar Rahul

Reputation: 342

sql server create dynamic columns runtime

in sql server create dynamic query to create columns for example

    declare @NoOFcolumns int=5
select name, [Col1], [col2], [col3], [col4], [col5]
from
(
  select c.name,
    cr.description,
    r.typeid
  from customers c
  left join rewards r
    on c.id = r.customerid
  left join customerrewards cr
    on r.typeid = cr.typeid
) x
pivot
(
  count(typeid)
  for description in ([Col1], [col2], [col3], [col4], [col5])
) p;

then add 5 columns

for eaxmple @NoOFcolumns int=10 how to add 10 columns by default

Upvotes: 1

Views: 92

Answers (1)

PSK
PSK

Reputation: 17953

You can get the distinct top (@NoOFcolumns) from you customerrewards tables for generating the dynamic pivot.

You can try using dynamic PIVOT like following.

DECLARE @NoOFcolumns int=5

DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT TOP (@NoOFcolumns)  ', ' 
                                 + Quotename([description] ) 
         FROM   customerrewards 
         FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 

DECLARE @query AS NVARCHAR(max) =
                 'select *
                        from
                        (
                            select c.name,
                            cr.description,
                            r.typeid
                            from customers c
                            left join rewards r
                            on c.id = r.customerid
                            left join customerrewards cr
                            on r.typeid = cr.typeid
                        ) x
                        pivot
                        (
                            count(typeid)
                            for description in IN ('+@cols+') 
                        ) p;'

EXECUTE(@query)

Note: I have not executed the query myself, but I feel it should work.

Upvotes: 1

Related Questions