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 |
|
Pete_N
Posting Yak Master
181 Posts |
 Posted - 2013-10-22 : 10:18:59
|
| SELECT [new_name] ,[new_ReasonCode] FROM [new_addacsreasoncodes]will produce new_name new_ReasonCode0 01 12 23 34 B5 C6 D7 E8 Rwhat I would like to get out isNameCol1 Reason Col1 NameCol2 ReasonCol2 NameCol3 ReasonCol3 0 0 3 3 6 D 1 1 4 B 7 E 2 2 5 C 8 R |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-10-22 : 10:44:29
|
| What is your criteria for where the data ends up?djj |
 |
|
|
Pete_N
Posting Yak Master
181 Posts |
Posted - 2013-10-22 : 10:45:25
|
| The data is for a tablix control on SSRS reporting |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-22 : 14:37:51
|
| I think the question meant what logic did you use to end up with 3 rows and 3 sets of name/code columns?Do you always want those 3 columns no matter how many rows are in the real table? So basically every 3 rows turns into 1 row?Be One with the OptimizerTG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-22 : 15:11:00
|
| [code]SELECT MAX(CASE WHEN (Seq -1)/3 = 0 THEN new_name END) AS NameCol1,MAX(CASE WHEN (Seq -1)/3 = 0 THEN new_Reasoncode END) AS ReasonCol1,MAX(CASE WHEN (Seq -1)/3 = 1 THEN new_name END) AS NameCol2,MAX(CASE WHEN (Seq -1)/3 = 1 THEN new_Reasoncode END) AS ReasonCol2,MAX(CASE WHEN (Seq -1)/3 = 2 THEN new_name END) AS NameCol3,MAX(CASE WHEN (Seq -1)/3 = 2 THEN new_Reasoncode END) AS ReasonCol3FROM(SELECT [new_name],[new_ReasonCode],ROW_NUMBER() OVER (ORDER BY new_name) AS SeqFROM [new_addacsreasoncodes])tGROUP BY (Seq-1) % 3[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
rearange data