Pivot table with one row and four columns

Question

 -- Pivot table with one row and four columns

SELECT 'Values' tValues, 
    ID,Name,ValueID,Value FROM (

        Select ID,Name,ValueID,Value FROM Table WHERE OptionID = 1000000

    ) AS SourceTable 

    PIVOT ( 

        COUNT(tValues)
        FOR tValues IN ( ID,Attribute,ValueID,Value ) 

    ) AS PivotTable;

I'm going off the example at Microsoft.com: http://msdn.microsoft.com/en-us/library/ms177410.aspx

But there are a few things about Pivot i don't really understand, so don't be surprised when you see it in the code above, such as COUNT(tValues), I have no idea what this is for, by judging from the example on microsoft, it seems to be always some sort of numeric value, so i figured i'd try it to see if it would return something, but all it returns is an error. Anyhow, if someone out there can share why this query doesn't work, and possibly explain what the numeric value above the FOR is used for?

The Table containts an x amount of rows, with four columns, so it looks like this:

 ID  |  Name   |  ValueID  |  Value

 100 |  Color  |  10000    |  Black
 101 |  Size   |  10005    |  Large

The output should be like this:

 Name_100  |  Color   |  Name_101  |  Size  |

 10000     |  Black   |  10005     |  Large |

Answer 1

Something like this maybe.

This will only work if the name column is unique. If not then you might want to append an id on it.

So first some test data:

CREATE TABLE tblValues
    (
        ID INT,
        Name VARCHAR(100),
        ValueID INT, 
        Value VARCHAR(100)
    )

INSERT INTO tblValues
VALUES
    (100,'Color',10000,'Black'),
    (101,'Size',10005,'Large')

Then you need to get the columns to pivot on:

DECLARE @cols VARCHAR(MAX)
;WITH CTE AS
(
    SELECT
        'Name_'+CAST(tbl.ID AS VARCHAR(100)) AS Name,
        'Name_'+CAST(tbl.ID AS VARCHAR(100)) AS Sort,
        tbl.ID
    FROM
        tblValues AS tbl
    UNION ALL
    SELECT
        tbl.Name,
        'Value_'+CAST(tbl.ID AS VARCHAR(100)) AS Sort,
        tbl.ID
    FROM
        tblValues AS tbl
)
SELECT
    @cols = COALESCE(@cols + ','+QUOTENAME(Name),
                 QUOTENAME(Name))
FROM
    CTE
ORDER BY
    CTE.ID,
    CTE.Sort

Then declaring and executing the dynamic sql like this:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        ''Name_''+CAST(tbl.ID AS VARCHAR(100)) AS pivotName,
        CAST(tbl.ValueID AS VARCHAR(100)) AS name
    FROM
        tblValues AS tbl
    UNION ALL
    SELECT
        tbl.Name AS pivotName,
        tbl.Value AS name
    FROM
        tblValues AS tbl
) AS p
PIVOT
(
    MAX(name)
    FOR pivotName IN ('+@cols+')
) AS pvt'

EXECUTE(@query)

Then in my case I will drop the table I have created

DROP TABLE tblValues

Edit

Or in you case it should be something like this:

First the columns:

DECLARE @cols VARCHAR(MAX)
;WITH CTE AS
(
    SELECT
        'Name_'+CAST(tbl.ID AS VARCHAR(100)) AS Name,
        'Name_'+CAST(tbl.ID AS VARCHAR(100)) AS Sort,
        tbl.ID
    FROM
        [Table] AS tbl
    WHERE
        tbl.OptionID = 1000000
    UNION ALL
    SELECT
        tbl.Name,
        'Value_'+CAST(tbl.ID AS VARCHAR(100)) AS Sort,
        tbl.ID
    FROM
        [Table] AS tbl
    WHERE
        tbl.OptionID = 1000000
)
SELECT
    @cols = COALESCE(@cols + ','+QUOTENAME(Name),
                 QUOTENAME(Name))
FROM
    CTE
ORDER BY
    CTE.ID,
    CTE.Sort

Then the dynamic sql.

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        ''Name_''+CAST(tbl.ID AS VARCHAR(100)) AS pivotName,
        CAST(tbl.ValueID AS VARCHAR(100)) AS name
    FROM
        [Table] AS tbl
    WHERE
        tbl.OptionID = 1000000
    UNION ALL
    SELECT
        tbl.Name AS pivotName,
        tbl.Value AS name
    FROM
        [Table] AS tbl
    WHERE
        tbl.OptionID = 1000000
) AS p
PIVOT
(
    MAX(name)
    FOR pivotName IN ('+@cols+')
) AS pvt'

EXECUTE(@query)

You do not need to create the table or drop the table. That was just because I did not have your table in my database and that if someone else want's to run the example.

Answer 2

If you want to use Pivot tables with a variable number of columns, then I'd suggest using something along the lines of;

   DECLARE @cols VARCHAR(4000)
   DECLARE @query VARCHAR(8000)
   SELECT  @cols = STUFF(( SELECT DISTINCT
            '],[' + Name
                       FROM    Table
                       ORDER BY '],[' + Name
                       FOR XML PATH('')
                     ), 1, 2, '') + ']'

   SET @query =
   'SELECT * FROM
   (
        SELECT col1, col2, col3, whateverColYourInterestedIn, Name, Value
        FROM Table
   )t
   PIVOT (MAX(Value) FOR Name
   IN ('+@cols+')) AS pvt'

   EXECUTE (@query)

That is probably not quite right, but it should hopefully be a starting point for you.

For more info, check out links such as this or this.