SQL combine column names and data types with column values

Question

I am using Sql Server 2012 for my DBMS.

In my database I have a Product table that is related to a series of catalog tables. These catalog tables represent the various Product Categories. The idea being that while all products have certain attributes in common (our internal identifier for it, a supplier, a cost, etc) the specifics of the products will vary (furniture is not described the same way as a digital product would be described).

Additionally, each product has a parent entity. A parent may have multiple children (or products in this case).

My task is to select the products and related information for a given list of parent Id's and populate that information into an XML document.

The tables I'm currently working with are:

PRODUCT
PRODUCT_DIGITAL
PRODUCT_FURNITURE

Product has a PK/FK relationship with PRODUCT_DIGITAL on Product_Id. Same type of relationship exist for PRODUCT to PRODUCT_FURNITURE.

If product looks like this:

PRODUCT_ID -- PRODUCT_CATEGORY -- PARENT_ID -- PARENT_TYPE -- DELIVERY_IN_DAYS
100           DIG                 1            1              7
101           DIG                 1            1              8
102           DIG                 1            1              1
103           DIG                 2            1              2
104           DIG                 2            1              1

And PRODUCT_DIGITAL looks like this:

PRODUCT_ID -- PRODUCT_TYPE -- PRODUCT_NAME -- PRODUCT_MNEMONIC
100           A               IMG1            IMAWTRFL
101           B               SND1            SNDENGRV
102           B               SND2            SNDHRSLF
103           A               IMG2            IMGNBRTO
104           B               SND3            SNDGTWNE

In the end I want result set that looks like this:

PRODUCT_CATEGORY -- PRODUCT_ID -- PRODUCT_TYPE -- PARENT_ID -- DELIVERY_IN_DAYS -- PROD_EXTENSION_NAME -- PROD_EXTENSION_TYPE -- PROD_EXTENSION_VALUE
DIG                 100           A               1            7                   PRODUCT_NAME           STRING                 IMG1
DIG                 100           A               1            7                   PRODUCT_MNEMONIC       STRING                 IMAWTRFL
DIG                 101           B               1            8                   PRODUCT_NAME           STRING                 SND1
DIG                 101           B               1            8                   PRODUCT_MNEMONIC       STRING                 SNDENGRV
DIG                 102           B               1            1                   PRODUCT_NAME           STRING                 SND2
DIG                 102           B               1            1                   PRODUCT_MNEMONIC       STRING                 SNDHRSLF
DIG                 103           A               2            2                   PRODUCT_NAME           STRING                 IMG2
DIG                 103           A               2            2                   PRODUCT_MNEMONIC       STRING                 IMGNBRTO
DIG                 104           B               2            1                   PRODUCT_NAME           STRING                 SND3
DIG                 104           B               2            1                   PRODUCT_MNEMONIC       STRING                 SNDGTWNE

My original searching brought me to UNPIVOT - but so far I have not been able to get my head around it. What I ended up doing was creating a temp table and updating it in passes, then joining back to the products table for the final select:

create table #tbl(product_id int, prod_extension_name varchar(100), prod_extension_type varchar(100), prod_extension_value varchar(1000))

insert into #tbl
select p.product_id, c.column_name, 
case c.data_type
when 'varchar' then 'string'
else data_type end as data_type
, null
from dbo.product p, information_schema.columns c
where c.table_name = 'PRODUCT_DIGITAL'
and c.column_name in ('PRODUCT_NAME','PRODUCT_MNEMONIC')

update #tbl
set prod_extension_value = p.product_name
from dbo.product p
where #tbl.product_id = p.product_id
and #tbl.colname = 'PRODUCT_NAME'

update #tbl
set prod_extension_value = p.product_mnemonic
from dbo.product p
where #tbl.product_id = p.product_id
and #tbl.colname = 'PRODUCT_MNEMONIC'

select p.product_category, p.product_id, pd.product_category, #tbl.prod_extension_name, #tbl.prod_extension_type, #tbl.prod_extension_value
from dbo.product p inner join dbo.product_digital pd on p.product_id = pd.product_id
inner join #tbl on p.product_id = #tbl.product_id
order by product_id

Can someone point me to a better way to do this? It seems like I should be able to do this faster, without having to make multiple updates, etc.

Taryn · Accepted Answer · 2013-01-10T14:39:29.077

I think this will do what you want. This implements the UNPIVOT function and then get the column information from the information_schema.columns view to get the result:

select product_id,
  product_category,
  parent_id,
  delivery_in_days,
  PROD_EXTENSION_NAME,
  case when c.data_type = 'varchar' 
      then 'STRING' else null end as PROD_EXTENSION_TYPE,
  PROD_EXTENSION_VALUE
from
(
  select 
    p.product_id,
    p.product_category,
    p.parent_id,
    p.delivery_in_days,
    PRODUCT_NAME,
    PRODUCT_MNEMONIC
  from product p
  left join product_digital pd
    on p.product_id = pd.product_id
) src
unpivot
(
  PROD_EXTENSION_VALUE
  for PROD_EXTENSION_NAME in (PRODUCT_NAME, PRODUCT_MNEMONIC)
) up
inner join 
(
  select c.column_name, c.data_type
    from information_schema.columns c
    where c.table_name = 'PRODUCT_DIGITAL'
      and c.column_name in ('PRODUCT_NAME','PRODUCT_MNEMONIC')
) c
  on up.PROD_EXTENSION_NAME = c.column_name

See SQL Fiddle with Demo

The result is:

| PRODUCT_ID | PRODUCT_CATEGORY | PARENT_ID | DELIVERY_IN_DAYS | PROD_EXTENSION_NAME | PROD_EXTENSION_TYPE | PROD_EXTENSION_VALUE |
-----------------------------------------------------------------------------------------------------------------------------------
|        100 |              DIG |         1 |                7 |        PRODUCT_NAME |              STRING |                 IMG1 |
|        100 |              DIG |         1 |                7 |    PRODUCT_MNEMONIC |              STRING |             IMAWTRFL |
|        101 |              DIG |         1 |                8 |        PRODUCT_NAME |              STRING |                 SND1 |
|        101 |              DIG |         1 |                8 |    PRODUCT_MNEMONIC |              STRING |             SNDENGRV |
|        102 |              DIG |         1 |                1 |        PRODUCT_NAME |              STRING |                 SND2 |
|        102 |              DIG |         1 |                1 |    PRODUCT_MNEMONIC |              STRING |             SNDHRSLF |
|        103 |              DIG |         2 |                2 |        PRODUCT_NAME |              STRING |                 IMG2 |
|        103 |              DIG |         2 |                2 |    PRODUCT_MNEMONIC |              STRING |             IMGNBRTO |
|        104 |              DIG |         2 |                1 |        PRODUCT_NAME |              STRING |                 SND3 |
|        104 |              DIG |         2 |                1 |    PRODUCT_MNEMONIC |              STRING |             SNDGTWNE |

Edit #1: If you want to perform this type of transformation dynamically then you will need to use dynamic SQL. to do this you will use the following.

First, you will need to get the list of columns to UNPIVOT:

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('PRODUCT_DIGITAL') and
               C.name not in ('PRODUCT_ID', 'PRODUCT_TYPE') -- include the items you DO NOT want to unpivot
         for xml path('')), 1, 1, '')

The final script will be:

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

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('PRODUCT_DIGITAL') and
               C.name not in ('PRODUCT_ID', 'PRODUCT_TYPE')
         for xml path('')), 1, 1, '')

select @cols = stuff((select ', '''+C.name + ''''
         from sys.columns as C
         where C.object_id = object_id('PRODUCT_DIGITAL') and
               C.name not in ('PRODUCT_ID', 'PRODUCT_TYPE')
         for xml path('')), 1, 1, '')

set @query 
  = '  select
          product_id,
          product_category,
          parent_id,
          delivery_in_days,
          PROD_EXTENSION_NAME,
          case when c.data_type = ''varchar'' 
              then ''STRING'' else null end as PROD_EXTENSION_TYPE,
          PROD_EXTENSION_VALUE
        from 
        (
          select 
            p.product_id,
            p.product_category,
            p.parent_id,
            p.delivery_in_days,
            PRODUCT_NAME,
            PRODUCT_MNEMONIC
          from product p
          left join product_digital pd
            on p.product_id = pd.product_id
        ) src
        unpivot
        (
          PROD_EXTENSION_VALUE
          for PROD_EXTENSION_NAME in ('+ @colsunpivot +')
        ) up
        inner join 
        (
          select c.column_name, c.data_type
          from information_schema.columns c
          where c.table_name = ''PRODUCT_DIGITAL''
            and c.column_name in ('+@cols+')
        ) c
          on up.PROD_EXTENSION_NAME = c.column_name'


exec(@query)

See SQL Fiddle with Demo. This will produce the same result as the original version except it is dynamic.

This helps me immensely. Can you offer any suggestions if the list of columns I'm transforming is dynamic? I limited the list for testing, but in reality there are anywhere from 2 to 6 columns that would be transformed. — trenthaynes, Jan 10 '13 at 14:24
@whipdancer see my edit which includes a dynamic sql version of the original code. — Taryn, Jan 10 '13 at 14:39
your example is so helpful - I think I'm finally getting my head around UNPIVOT (more reading and experimenting to do). I wish I could up vote this +100. Thank you! — trenthaynes, Jan 10 '13 at 15:08
@whipdancer you are welcome. `Pivot` and `Unpivot` are great functions. Just so you know this could also be done using a `UNION ALL` instead of `UNPIVOT` they do the same thing. :) — Taryn, Jan 10 '13 at 15:11

score 0 · Answer 2 · answered Jan 10 '13 at 12:00

I might be jumping the gun, but you say that your final output should be XML. While I have not included all your requirements, would something like this suit your requirements? (I think I may have over simplified your requirements)

;WITH PRODUCT (PRODUCT_ID, PRODUCT_CATEGORY, PARENT_ID, PARENT_TYPE, DELIVERY_IN_DAYS) AS
(
    SELECT 100, 'DIG', 1, 1, 7  UNION ALL
    SELECT 101, 'DIG', 1, 1, 8  UNION ALL
    SELECT 102, 'DIG', 1, 1, 1  UNION ALL
    SELECT 103, 'DIG', 2, 1, 2  UNION ALL
    SELECT 104, 'DIG', 2, 1, 1
)
,PRODUCT_DIGITAL (PRODUCT_ID, PRODUCT_TYPE, PRODUCT_NAME, PRODUCT_MNEMONIC) AS
(
    SELECT 100, 'A', 'IMG1', 'IMAWTRFL' UNION ALL
    SELECT 101, 'B', 'SND1', 'SNDENGRV' UNION ALL
    SELECT 102, 'B', 'SND2', 'SNDHRSLF' UNION ALL
    SELECT 103, 'A', 'IMG2', 'IMGNBRTO' UNION ALL
    SELECT 104, 'B', 'SND3', 'SNDGTWNE'
)

SELECT   P.PRODUCT_CATEGORY
        ,P.PRODUCT_ID
        ,P.PARENT_TYPE
        ,P.PARENT_ID
        ,P.DELIVERY_IN_DAYS
        ,PD.PRODUCT_NAME
        ,PD.PRODUCT_MNEMONIC
FROM PRODUCT            P
JOIN PRODUCT_DIGITAL    PD  ON P.PRODUCT_ID = PD.PRODUCT_ID
FOR XML PATH

I tried that originally (as the final select from my example) and the xml it produces is not compliant with the XSD. — trenthaynes, Jan 10 '13 at 14:09

SQL combine column names and data types with column values

2 Answers2