sql server create dynamic columns runtime

Question

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

You can easily have dynamic SQL SEELECT command by building your command in a varchar, i.e. @DYNSQLCOM and using "EXEC sp_executesql @DYNSQLCOM" learn.microsoft.com/it-it/sql/relational-databases/… — A. Lion
– A. Lion, Commented Jan 30, 2019 at 8:26

PSK · Accepted Answer · 2019-01-30 08:31:57Z

1

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.

edited Jan 30, 2019 at 8:31

answered Jan 30, 2019 at 8:25

PSK

18k5 gold badges35 silver badges44 bronze badges

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

1 Comment

user1694674 Over a year ago

Maybe even change the select * to select name' + @cols +' I was about to suggest

Collectives™ on Stack Overflow

sql server create dynamic columns runtime

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related