We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

CONVERT ROWS TO COLUMNS IN SQLServer 2008

mojian
mojian asked
on
Medium Priority
425 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

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

Author

Commented:
can i use pivot to do this?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
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;

Author

Commented:

Hi! PatelAlpesh,

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

Incorrect syntax  near keyword 'AND'

Author

Commented:
Any one help me ??
please.

Author

Commented:
i  do not know how to solve it.any one hep me?
Keep on reading docs & examples
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
not sure if you can use PIVOT for this, I am not a "master" in PIVOT statement ...
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
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;

Author

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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.