lemunk lemunk - 4 months ago 36
SQL Question

SQL Server 2008 CrossTab equivalent

Afternoon Guys n Girls.

Using SQL SERVER 2008.

I have a table called userModList. Its contains fields, "USERID"(int), "ModuleID"(int), and "Passed"(bin).
example data;

USERID ModuleID Passed
134 12 1
134 10 0
134 18 1


What i would like to display is:

USERID (moduleNum12) (ModuleNum10) (ModuleNum18)
134 1 0 1


Now In MS access all you would do is create a cross query, so User ID becomes the row, Module numbers become the Columns and Passed is the values (binary 1 or 0).

I would like to do this server Side in a Stored Procedure, but I have never Attempted cross tabbing data.

Also The moduleID is dynamic meaning there may be 3 modules for a user or 17. So it needs to be dynamic, not sure if this makes a big difference?

Anyway some help on this would be great, ill try and provide some sample code Of what I will try, But as it stands I'm stuck as to where to start.

many thanks guys!

Answer

There are a few different ways that you can do this in SQL Server you can use the PIVOT function:

select userid,
  [12] moduleNum12,
  [10] moduleNum10,
  [18] moduleNum18
from
(
  select userid, moduleid, cast(passed as int) passed
  from yourtable
) d
pivot
(
  max(passed)
  for moduleId in ([12], [10], [18])
) piv;

See Demo

Or you can use an aggregate function with a CASE expression:

select userid,
  max(case when moduleid = 12 then cast(passed as int) end) moduleNum12,
  max(case when moduleid = 10 then cast(passed as int) end) moduleNum10,
  max(case when moduleid = 18 then cast(passed as int) end) moduleNum18
from yourtable
group by userid;

See Demo.

The above work great if the values are known nut if you have unknown values, then you will need to use dynamic SQL:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ModuleID) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsAlias = STUFF((SELECT distinct ', ' + QUOTENAME(ModuleID) +' as moduleNum'+cast(ModuleID as varchar(10))
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT userid,' + @colsAlias + ' 
            from
            (
               select userid, moduleid, cast(passed as int) passed
               from yourtable 
            ) d
            pivot 
            (
                max(passed)
                for moduleid in (' + @cols + ')
            ) p '

execute(@query)

See Demo