[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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
0
mojian
Asked:
mojian
  • 5
  • 2
  • 2
  • +3
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now