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 |
|
kond.mohan
Posting Yak Master
213 Posts |
 Posted - 2012-06-07 : 03:58:57
|
| dear all i am executing sql query i want to know which way to fetch the data below mentioned manneri have a table my table structue below mentioned empcode comp_type comp_name b006 , C, C 4 Fairness b006 , H, K 4 Helps out in Re b006 , C, C 4 solving& process b006 , C, C 4 sdfadf b006 , H, k 4 performs task b006 , H, k 5 achiveing tasks &wyse b006 , H, k15 word compare feedback b006 , C, C 4 Fairness b006 , H, k 4 performs assingendmy expected output is:emp code c1 c2 .cn k1 .knbg006 C 4 Fairness C 4 solving& process .. K 4 Helps out in Re comp_name shold get in 9 columns separately anybody know explain me the logic |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-07 : 04:16:52
|
| Do you have a maximum number of entries per empcode?Do you want the duplicates?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
kond.mohan
Posting Yak Master
213 Posts |
Posted - 2012-06-07 : 05:05:23
|
| HERE EMPCODE COLUMN HAVING MULTIPLE TYPES(NOT DUPLICATES) OF COMP_NAMESSO I NEED THIS MULTIPLE TYPES ARE SEPARATE,SEPARATE COLUMN ...EXPECTED OUT PUT ISEMPCODE K1 ,K2 ,K3 , K4 ,C1, C2, C3, C4 B006 K1NE ,SDF, K1DF,K4GFG, C 4 Fair,C4INTEGRIT, C4TEAM,C4CO |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-07 : 05:22:33
|
| b006 , C, C 4 Fairnessappears twice.alsob006 , H, K 4 Helps out in Re b006 , C, C 4 solving& processAre there 2 entries in the comp_name column including the comp_type for the second?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-07 : 05:26:14
|
| maybe;with cte as(select *, seq = row_number() over (partition by empcode, comp_type order by comp_name))select empcode, k1 = max(case when comp_type = 'k' and seq = 1 then comp_name else '' end), k2 = max(case when comp_type = 'k' and seq = 2 then comp_name else '' end), k3 = max(case when comp_type = 'k' and seq = 3 then comp_name else '' end), k4 = max(case when comp_type = 'k' and seq = 4 then comp_name else '' end), c1 = max(case when comp_type = 'c' and seq = 1 then comp_name else '' end), c2 = max(case when comp_type = 'c' and seq = 2 then comp_name else '' end), c3 = max(case when comp_type = 'c' and seq = 3 then comp_name else '' end), c4 = max(case when comp_type = 'c' and seq = 4 then comp_name else '' end)from ctegroup by empcode==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
kond.mohan
Posting Yak Master
213 Posts |
Posted - 2012-06-07 : 06:10:16
|
| here i executed above metioned queryhere i got only c1,c2,c3,c4 colummns(same value is showing each row )and k1,k2,k3,k4 columns are blanki did n't get the proper outputmohan |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-07 : 06:39:47
|
| Think yoou need to post some sample data.Declare a table variable and populate it with some data.From your first post the comp_type is h and c - shoould this be translated to k and c?That's probably why you are missing the k's - just change k to h in the query.As to why the same value appears in all rows - I suspect there are duplicates and 4 of the first one?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
sql server 2008(ssms)