CODEWITHSUNDEEP
sqlsql-serverpivot
user3165474
user3165474

Reputation: 164

SQL PIVOT: One To Many

I have 2 table in SQL.

dbo.main

|id | mid | tid 
 1    yes    no

dbo.external

| id | type | element |
  1    c        9
  1    d        10
  1    h        11
  1    g        12

What i try to achieve is :

| id | mid | tid | c | d | h | g  |
  1    yes    no   9  10  11   12

i try to use pivot on dbo.external statment as 

SELECT *
FROM 
(
  SELECT id,type,element
  FROM dbo.external
) SRC
PIVOT
(
  MAX(FIELD_DATA)
  FOR FIELD_NUMBER IN (id,type,element)
) PIV;

The problem is: how can i pivot dbo.external then join the dbo.main in one sql statement?

Upvotes: 5

Views: 948

Answers (2)

Luis Teijon
Luis Teijon

Reputation: 4899

Use WITH clause:

WITH temp AS(
   -- here make the join
)
SELECT id,mid,tid, c,d,h,g
FROM temp
PIVOT
(
  MAX(FIELD_DATA)
  FOR type IN (c,d,h,g)
) as PIV;

Upvotes: 4

Pரதீப்
Pரதீப்

Reputation: 93764

Just JOIN the Main table in Pivot source query

SELECT * 
FROM   (SELECT e.id,mid,tid,[element],[type] 
        FROM   dbo.[external] e 
               JOIN main m 
                 ON e.id = m.id) a 
       PIVOT ( Max([element]) 
             FOR [type] IN ([c],[d],[h],[g]) ) PIV

Upvotes: 5

Related Questions