Dynamic pivot data with multiple datatypes

Question

I have a trick problem with a pivot table to make:

I have a table which looks like:

id  table   object  name     type   nvarchar    date    int     bit  
1   1       2       name     1      tables      NULL    NULL    NULL  
2   1       2       name     1      columns     NULL    NULL    NULL  
3   1       2       name     1      datatypes   NULL    NULL    NULL  
4   1       2       name     1      _users      NULL    NULL    NULL  
1   1       3       active   3      NULL        NULL    NULL    1  
2   1       3       active   3      NULL        NULL    NULL    1  
3   1       3       active   3      NULL        NULL    NULL    1  
4   1       3       active   3      NULL        NULL    NULL    1  

the output should look like:

id   name       active   
1    tables     1  
2    columns    1  
3    datatypes  1  
4    _users     1  

Based upon the "type" I should put the correct data from the column in it, these columns are formated in nvarchar, bit, datetime, int, ect.

The "id" is the row id, the "name, active" comes from the name column and the values from nvarchar, date, int and bit columns.

UPDATE: the columns like nvarchar, date, int and bit (and most other SQL formats) are actually contain this type of data. The column "type" gives which column contains the data to being used, so if "type" is "1", than I want to use the "nvarchar" if "type" is "3" than I want to use the "bit" which contains really a bit and not a nvarchar. In the Pivot I want to have the bit under "active" column, if I have in the example a 3th column (name) for example "activation_date" I want to see a third column with the value (type = 2) from the date column.

I am lost in this, please help

Answer 1

Assuming there's only one not null column for each row:

with cte as (
    select
        id,
        name,
        coalesce(
            [nvarchar],
            convert(nvarchar(max), [date], 120),
            cast([int] as nvarchar(max)),
            cast([bit] as nvarchar(max))
        ) as value
    from Table1 as t
)
select
    id,
    max(case when [name] = 'name' then value end) as [name],
    max(case when [name] = 'active' then value end) as [active]
from cte
group by id

sql fiddle demo

But I must warn you, this types of database schema is not best way to use SQL.

If you want to do this dynamically without hardcoding columns:

declare @stmt nvarchar(max)

select @stmt =
   isnull(@stmt + ', ', '') +
   'max(case when [name] = ''' + name + ''' then value end) as ' + quotename([name])
from (select distinct [name] from Table1) as t

select @stmt = '
with cte as (
    select
        id,
        name,
        coalesce(
            [nvarchar],
            convert(nvarchar(max), [date], 120),
            cast([int] as nvarchar(max)),
            cast([bit] as nvarchar(max))
        ) as value
    from Table1 as t
)
select
    id, ' + @stmt + '
from cte
group by id
'

exec sp_executesql
    @stmt = @stmt

sql fiddle demo

If you have some Mapping table like this:

name       value
--------------------
name       nvarchar
active     bit

you can use this query:

declare @stmt nvarchar(max)

select @stmt =
   isnull(@stmt + ', ', '') +
   'max(case when [name] = ''' + name + ''' then [' + value + '] end) as ' + quotename([name])
from Mapping

select @stmt = '
select
    id, ' + @stmt + '
from Table1
group by id
'

exec sp_executesql
    @stmt = @stmt

sql fiddle demo