CONVERT ROWS TO COLUMNS IN SQLServer 2008

Hi,

1.table
ID     NAME    BIRTH
----------------------------
1      JOHN    19510102
2.     MARY   19860321

2. table
ID     ATTR        ICODE   INAME
--------------------------------------
1      CR1           1        build1
1      CR2           1        room1          
1      CT1           001     A
1      CK1           0001   abc
2      CR1           2       build2
2      CR2           22     room22
2      CT1           002    B

I need table like this
ID NAME   BIRTH        CR1  CR1_NAME   CR2    CR2_NAME  CT1,  CT1_NAME      CK1   CK1_NAME
1  JOHN    19510102   1    build1              1         room1         001     A                   0001   abc
2  MARY    19860321  2    build2             22        room22      002      B


Thx in advance
mojianAsked:
Who is Participating?
 
ZtinelConnect With a Mentor Commented:
   Actually the code of AngelIII is correct (with very slight modification)
select t1.id, t1.name, t1.birth
  , max( case when t2.ATTR = 'CR1' THEN t2.ICODE END) CR1
  , max( case when t2.ATTR = 'CR1' THEN t2.INAME END) CR1_NAME

  , max( case when t2.ATTR = 'CR2' THEN t2.ICODE END) CR2
  , max( case when t2.ATTR = 'CR2' THEN t2.INAME END) CR2_NAME

  , max( case when t2.ATTR = 'CK1' THEN t2.ICODE END) CK1
  , max( case when t2.ATTR = 'CK1' THEN t2.INAME END) CK1_NAME

 from table1 t1
 left join table2 t2
   ON t2.ID = t1.ID
group by t1.id, t1.name, t1.birth

Open in new window


   But if you want to to use pivot, try this code
SELECT a.id, a.[name], a.birth, a.[cr1] AS cr1, b.[cr1] AS cr1_name, a.[cr2] AS cr2, b.[cr2] AS cr2_name, a.[ct1] AS ct1, b.[ct1] AS ct1_name, a.[ck1] AS ck1,b.[ck1] AS ck1_name
FROM 
(SELECT id, [name], birth, [cr1], [cr2], [ct1], [ck1] FROM
(SELECT a.id, [name], birth, b.attr as attr, b.icode
FROM #table1 a join #table2 b 
on a.id = b.id) p
PIVOT
(MAX(icode) FOR attr IN
( [cr1], [cr2], [ct1], [ck1] )) AS pvt) AS a
INNER JOIN
(SELECT id, [name], birth, [cr1], [cr2], [ct1], [ck1] FROM
(SELECT a.id, a.[name], a.birth, b.attr as attr, b.iname
FROM #table1 a join #table2 b 
on a.id = b.id) p
PIVOT
(max(iname) FOR attr IN ([cr1], [cr2], [ct1], [ck1])) AS pvt1) as b
on a.id = b.id

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this shall do:
select t1.id, t1.name, t2.birth
  , max( case when t2.ATTR = 'CR1' THEN t2.ICODE END) CR1
  , max( case when t2.ATTR = 'CR1' THEN t2.INAME END) CR1_NAME

  , max( case when t2.ATTR = 'CR2' THEN t2.ICODE END) CR2
  , max( case when t2.ATTR = 'CR2' THEN t2.INAME END) CR2_NAME

  , max( case when t2.ATTR = 'CK1' THEN t2.ICODE END) CK1
  , max( case when t2.ATTR = 'CK1' THEN t2.INAME END) CK1_NAME

 from table1 t1
 left join table2 t2
   ON t2.ID = t1.ID
group by t1.id, t1.name, t2.birth

Open in new window

0
 
mojianAuthor Commented:
can i use pivot to do this?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Alpesh PatelAssistant ConsultantCommented:
Please use pivot table

SELECT ID, NAME, BIRTH,  [3] as CR1, [4] as  CR1_NAME, [5] as  CR2, [6] as CR2_NAME  [7] as CT1, [8] as CT1_NAME , [9]  as    CK1, [10] as   CK1_NAME
FROM
(SELECT Table1.Id, Table1.Name, Table1.Birth, Table2.Attr, Table2.IName
FROM Tabl1 left outer join Table2 on Table1.ID = Table2.ID) p
PIVOT
(
COUNT (ID)
FOR ATTR        IN
( [3], [5], [7], [9] )
AND INAME IN
( [4], [6], [8], [10] )
) AS pvt
ORDER BY pvt.D;
0
 
mojianAuthor Commented:

Hi! PatelAlpesh,

i try you SQL sentences.but i get  error like this

Incorrect syntax  near keyword 'AND'
0
 
mojianAuthor Commented:
Any one help me ??
please.
0
 
mojianAuthor Commented:
i  do not know how to solve it.any one hep me?
0
 
Kobe_LenjouCommented:
Keep on reading docs & examples
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure if you can use PIVOT for this, I am not a "master" in PIVOT statement ...
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT ID, NAME, BIRTH,  [3] as CR1, [4] as  CR1_NAME, [5] as  CR2, [6] as CR2_NAME  [7] as CT1, [8] as CT1_NAME , [9]  as    CK1, [10] as   CK1_NAME
FROM
(SELECT Table1.Id, Table1.Name, Table1.Birth, Table2.Attr, Table2.IName
FROM Tabl1 left outer join Table2 on Table1.ID = Table2.ID) p
PIVOT
(
COUNT (ID)
FOR ATTR        IN
( [3], [5], [7], [9] )
) AS pvt
ORDER BY pvt.D;
0
 
mojianAuthor Commented:
Hi,PatelAlpesh,

i try the sql like this
SELECT ID, NAME, BIRTH,  [3] as CR1, [4] as  CR1_NAME, [5] as  CR2, [6] as CR2_NAME  [7] as CT1, [8] as CT1_NAME , [9]  as    CK1, [10] as   CK1_NAME
FROM
(SELECT Table1.Id, Table1.Name, Table1.Birth, Table2.Attr, Table2.IName
FROM Tabl1 left outer join Table2 on Table1.ID = Table2.ID) p
PIVOT
(
COUNT (ID)
FOR ATTR        IN
( [3], [5], [7], [9] )
) AS pvt
ORDER BY pvt.D;



but it returns the error like
[4] is not valid,
[6] is not valid
[8] is not valid
[10] is not valid

pls help me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.