CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlpivot
Josh Stodola
Josh Stodola

Reputation: 82523

Constructing a PIVOT

I think that PIVOT will help me accomplish this, but I can't get anything started. I am having serious SQL brain farts today, I need some help.

Here is the output I have now:

Id    Name  Question    Answer
0     Test  Vault       A
0     Test  Container   1
1     Foo   Vault       B
1     Foo   Container   2

And this is my desired output:

Id   Name Vault Container
0    Test A    1
1    Foo  B    2

Can this be done?

If that is impossible or terribly complex to do, I have an alternate way to approach this. The output for my alternate query is:

Id   Name VaultId ContainerId
0    Test A       NULL
0    Test NULL       1
1    Foo  B       NULL  
1    Foo  NULL       2

And here I need to be able to suppress it into one row per Id/Name. I can't remember how to do either of these!

Upvotes: 3

Views: 351

Answers (3)

Mathew Thompson
Mathew Thompson

Reputation: 56459

Yeah PIVOT is what you need here :). Assuming your table is called MyPivot Try:

SELECT Id, Name, [Vault], [Container]
FROM (SELECT Id, Name, Question, Answer FROM MyPivot) AS SourceTable
PIVOT (MAX(Answer) FOR Question in (Vault, Container)) as p;

EDIT: To demonstrate what that syntax means, see the following breakdown:

PIVOT (<aggregate function>(<column being aggregated>) 
FOR <column that contains the values that will become column headers>
    IN ( [first pivoted column], [second pivoted column])

Upvotes: 2

Taryn
Taryn

Reputation: 247880

You could do this with a Static Pivot:

create table temp
(
    id int,
    name varchar(10),
    question varchar(10),
    answer  varchar(10)
)

INSERT into temp   VALUES (0,'test', 'vault','a');
INSERT into temp   VALUES (0,'test', 'Container','1');
INSERT into temp   VALUES (1,'foo', 'vault','b');
INSERT into temp   VALUES (1,'foo', 'Container','2');

select *
from 
(
    select id, name, question, answer
    from temp
) x
pivot 
(
    max(answer)
    for question in ([container], [vault])
) p

drop table temp

or a dynamic pivot

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.question) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, name, ' + @cols + ' from 
            (
                select id, name, question, answer
                from temp
           ) x
            pivot 
            (
                 max(answer)
                for question in (' + @cols + ')
            ) p '


execute(@query)

both will give you the same results:

enter image description here

Upvotes: 2

Royi Namir
Royi Namir

Reputation: 148744

DECLARE @Test TABLE
(
     Id      INT 
    ,[Name]VARCHAR(10) NOT NULL
    ,Question       VARCHAR(10) NOT NULL,
    Answer VARCHAR(10)

);  
INSERT  @Test   VALUES (0,'test1', 'vault','a');
INSERT  @Test   VALUES (0,'test1', 'Container ','1');
INSERT  @Test   VALUES (1,'test4', 'vault','b');
INSERT  @Test   VALUES (1,'test4', 'Container','2');



;WITH CTE
AS
(
    SELECT  t.id, t.[Name], t.[Question    ]   ,t.Answer
     FROM    @Test t
)

SELECT  * 
FROM    CTE
 PIVOT   ( max(answer) FOR Question     IN (vault,container) )  f;

enter image description here

Upvotes: 5

Related Questions