Geek asks and answers

Building a PIVOT

I think PIVOT will help me with this, but I cannot start anything. Today I have serious cardiac emissions of SQL, I need help.

Here is the result that I have:

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 result:

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

Can this be done?

If this is impossible or terribly difficult to do, I have an alternative way to approach this. Result for my alternative query:

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 in one line on Id / Name. I can’t remember how to do it!

+3
source share
3 answers
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

+5
source

Yes, PIVOTthat’s what you need :). Assuming your table is called MyPivotTry:

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: , , . :

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

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


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)

:

enter image description here

+2

More articles:


All Articles