user3165474 user3165474 - 2 months ago 19
SQL Question

SQL : 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?

Answer

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 
Comments