Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjcclee
Starting Member
33 Posts |
 Posted - 2013-07-15 : 18:11:00
|
| Hi, Dearl all, I need help on this query. I have table shown as below,each logID may have multiple types LOGID Types 1 Value1 1 Value3 2 Value3 2 Value5 3 Value8 4 Value5 I used dynamic SQL using pivot, get the result as below: LOGID Value1 Value3 Value5 Value8 1 Value1 Value3 null null 2 null Value3 Value5 null 3 null null null Value8 4 null null Value5 null This is not the format I want, I want it display as following: LOGID Column1 Columns2 Column3 Column4 1 Value1 Value3 null null 2 Value3 Value5 null null 3 Value8 null null null 4 Value5 null null null How to do it? Thank you! |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-15 : 20:27:21
|
| [CODE]; WITH CTE AS (SELECT LOGID, Types, ROW_NUMBER() OVER(PARTITION BY LOGID Order by Types) as RN FROM @Temp)SELECT T1.LOGID, T1.Types as Col1, T2.Types as Col2 FROM CTE T1 LEFT JOIN CTE T2 ON T1.LOGID = T2.LOGID and T1.RN = T2.RN-1 where T1.RN = 1;[/CODE] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
cjcclee
Starting Member
33 Posts |
Posted - 2013-07-16 : 13:08:26
|
| Thank you so much for both of you. It worked perfectly. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 13:40:21
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
convert rows into column dynamically