Eray Balkanli Eray Balkanli - 2 months ago 14
SQL Question

How to provide dynamic usage of select query in SQL Server 2008 based on a variable value

I have a table

myTable
with these columns and sample data:

Id Name Period0Status Period1Status Period2Status
-------------------------------------------------------
1 Mark 1 2 3
2 John 2 3 3
3 Brad 1 1 1
4 John 3 3 3
5 Mark 1 3 2
etc...


What I want is to use those data but the column names should be taken according to a SQL variable. What I need is something like that:

declare @0_period varchar(50) = 'Period_0'
declare @1_period varchar(50) = 'Period_1'
declare @2_period varchar(50) = 'Period_2'

declare @counter = 0;

while @counter < 3
begin
insert into #Results(Period, JohnVolume, MarkVolume, JoshVolume)
select
'@' + @counter + '_period',
sum(case when(name = 'John' and (Period+@counter+Status = 3) then 1 else 0)),
sum(case when(name = 'Mark' and (Period+@counter+Status = 3) then 1 else 0)),
sum(case when(name = 'Brad' and (Period+@counter+Status = 3) then 1 else 0))
from
myTable

set @counter = @counter + 1
end


I could not find the correct syntax to provide the dynamic query here. Any help would be appreciated. SQL Server 2008 is used.

Output table I want to produce be like:

Period JohnVolume MarkVolume BradVolume
Period_0 1 0 0
Period_1 2 1 0
Period_2 2 1 0


It should basically counts the amount of 3s for each (John,Mark and Brad) for each period. I gotta find out how to correct the syntax for the parts that @counter is used inside the select query.

ZLK ZLK
Answer

Here's an example of how you could do this, with a cross apply to derive the values for each period and a count(case...) for the end values:

SELECT period
     , COUNT(CASE WHEN Name = 'John' AND val = 3 THEN 1 END) [JohnVolume]
     , COUNT(CASE WHEN Name = 'Mark' AND val = 3 THEN 1 END) [MarkVolume]
     , COUNT(CASE WHEN Name = 'Brad' AND val = 3 THEN 1 END) [BradVolume]
FROM myTable
CROSS APPLY (VALUES ('Period_0', Period0Status), ('Period_1', Period1Status), ('Period_2', Period2Status)) unp(period, val)
GROUP BY period

The cross apply works as an unpivot to get each period status and the values within them, and the conditional case statements work as a pivot.

Alternatively, you could use an actual UNPIVOT/PIVOT if you want. An example would be:

SELECT Period, John JohnVolume, Mark MarkVolume, Brad BradVolume
FROM (
    SELECT *
    FROM myTable
    UNPIVOT (val FOR period IN (Period0Status, Period1Status, Period2Status)) U
    WHERE val = 3) T
PIVOT (COUNT(val) FOR Name IN ([John], [Mark], [Brad])) P
Comments