Split One row into two

Question

Split One row into two

SQL 321

I need to split one row into two: One for R_GrossAmt and one for S_GrossAmt. Also, for R_GrossAmt. I use an Union to do that, but was wondering whether this can also be done using CROSS APPLY? If so, how will be my query.

DECLARE @TableA TABLE
(UID varchar(10),
 Currency varchar(5),
 R_GrossAmt float,
 S_GrossAmt float)

INSERT INTO @TableA VALUES ('ABC123','USD',482.45,395.28)
INSERT INTO @TableA VALUES ('PQR321','USD',1245.97,1786.34)
INSERT INTO @TableA VALUES ('XYZ456','USD',2342.30,879.20)

 SELECT UID, Currency, R_GrossAmt, 'RETURN' AS Type FROM @TableA
 UNION ALL
 SELECT UID, Currency, S_GrossAmt, 'CONTRUBUTION' AS Type FROM @TableA
 ORDER BY UID, Type

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer accepted by question author

Viorel 127.3K

Check an alternative:

select *
from ( select UID, Currency, R_GrossAmt as [RETURN], S_GrossAmt as [CONTRIBUTION] from @TableA ) a
unpivot ( GrossAmt for Type in ( [RETURN], [CONTRIBUTION] ) ) u
order by UID, Type

0 comments

1 additional answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Vikas Singh 145

Yes, You can achieve it using CROSS APPLY. Here is the script-

SELECT c.*
FROM @TableA
cross apply (values ([UID], Currency, R_GrossAmt, 'RETURN'),
([UID], Currency, S_GrossAmt, 'CONTRUBUTION')
) c ([UID], Currency, S_GrossAmt, [Type] )
ORDER BY [UID], [Type]

0 comments