Hello All,

How do I align output from two columns into a single column? Below are the DDL/DML as required with sample data.

CREATE TABLE #Users(
UserID VARCHAR(7),
G1 VARCHAR(30),
G2 VARCHAR(30),
G3 VARCHAR(30),
G4 VARCHAR(30),
G5 VARCHAR(30)
)


INSERT INTO #Users (UserID,
G1,
G2,
G3,
G4,
G5)
VALUES ('a123', 'Training', 'Users', 'Accounts', 'ABC Company', 'ABC'),
('a456', 'Users', 'Accounts', 'ABC Company','ABC', ''),
('a789', 'Training', 'Users', 'Accounts', 'ABC Company', 'ABC')

Expect Output:

a123 | Training | Users | Accounts | ABC Company | ABC
a456 |             |Users   |Accounts  |ABC Company  |ABC 
a789 |Training |Users   |Accounts  |ABC Company   |ABC

T

You would need to write case statements to properly shift your data, e.g.

case when G5='' and G1<>'' and G2<>'' and G3<>'' and G4<> '' then G2 else G1 end as G1,

same expression for other columns. You may need to consider more different cases.

What is the logic to know to which group a value from different group belongs to?

For example, how do we know that the value "Users" in column G1 for [UserID] = 'a456' should be shifted to G2.

I have more than 10 columns. So, I'm not sure if I can write CASE statement for each and every record.

The basic logic is, all the 'Users' should be aligned into a single column which takes care of the remaining.

Try with output to text:

select UserId + '|' + case when G1 = 'Users' then space(8) + '|' + G1 else G1 end + '|' + G2 + '|' + G3 + '|' + G4 + case when G5<>'' then '|' else '' end + G5 as Output
from #Users

Thank you. That was very helpful. :-)

I modified it based on your query to output to a table itself.

SELECT UserID,
CASE WHEN G1 = 'Users' THEN  '' ELSE G1 END AS G1,
CASE WHEN G1 = 'Users' THEN  G1 ELSE G2 END AS G2,
CASE WHEN G1 = 'Users' THEN  G2 ELSE G3 END AS G3,	
CASE WHEN G1 = 'Users' THEN  G3 ELSE G4 END AS G4,
CASE WHEN G1 = 'Users' THEN  G4 ELSE G5 END AS G5
FROM #Users