SQL joined table pivot without aggregate

Question

I have two tables:

Table1
ID  TYPE
1   ABC1
2   ABC2
3   ABC3

Table2
ID  Data
1   100
1   101
2   10
2   90

And I want the results to look like this:

ID  Data1  Data2
1   100    101
2   10     90

But I'm having a total mare with my attempts at creating the pivot. So far I have:

With Inital_Data As ( 
Select 
A.ID,
B.Data As Data1,
B.Data As Data2

From 
Table1 A join
Table2 B on
A.ID = B.ID
) 

Select * 
From
 Initial_Data
 PIVOT
(Max(ID) FOR Data IN (Data1,Data2)) p

I know this is rubbish but so far even the logic of what I'm trying to achieve is escaping me, let alone the syntax! Can anyone give me a guiding hand?

Might be answered here for you: http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function — StoicFnord, Sep 01 '15 at 09:25

Shnugo · Accepted Answer · 2015-09-02T11:19:20.777

Pivot with more than one column needs some tricks, I prefer the XML concatenation. First I take all values for each ID in only one XML, then you can take these values one by one:

Just paste this into an empty query window and execute. Adapt for your needs

EDIT: Includes column Type from TABLE1 as Caption for ID...

DECLARE @Table1 TABLE(ID INT,[TYPE] VARCHAR(10));
INSERT INTO @Table1 VALUES
 (1,'ABC1')
,(2,'ABC2')
,(3,'ABC3');

DECLARE @Table2 TABLE(ID INT,DATA INT);
INSERT INTO @Table2 VALUES
 (1,100)
,(1,101)
,(2,10)
,(2,90);

WITH DistinctIDs AS
(
    SELECT DISTINCT tbl2.ID,tbl1.[TYPE] 
    FROM @Table2 AS tbl2
    INNER JOIN @Table1 AS tbl1 ON tbl1.ID=tbl2.ID
)
SELECT ID,[TYPE]
      ,concatXML.x.value('/root[1]/item[1]/@data','int') AS Data1
      ,concatXML.x.value('/root[1]/item[2]/@data','int') AS Data2
FROM DistinctIDs AS dIDs
CROSS APPLY
(
    SELECT ID AS [@id],DATA AS [@data]
    FROM @Table2 AS tbl WHERE tbl.ID=dIDs.ID
    FOR XML PATH('item'), ROOT('root'),TYPE
) AS concatXML(x)

@MattHollands, if this is the solution for you, please vote up and/or mark as accepted if helpful... If not, just ask! — Shnugo, Sep 01 '15 at 12:45
Sorry Shnugo, the results should have had the Type column from Table 1 in the first column. Thanks for your answer. I will try it out today. — Matt Hollands, Sep 02 '15 at 07:43
@MattHollands, edited my code to get the [TYPE] column within the result. Please vote up if helpful, thx — Shnugo, Sep 02 '15 at 11:20

SQL joined table pivot without aggregate

1 Answers1