Combining Fields

Transact-SQL
1
ID First_Value Second_Value
1 55 100
2 55 100
3 55 100
4 55 100
5 55 100

Is the best way to convert the above to the below is to use a Union? If there is a better way of writing this, please let me know.

ID Type Values
1 First_Value 55
2 First_Value 55
3 First_Value 55
4 First_Value 55
5 First_Value 55
1 Second_Value 100
2 Second_Value 100
3 Second_Value 100
4 Second_Value 100
5 Second_Value 100 
SELECT
	  ID
	, 'First_Value'		AS Type
	, First_Value		AS Values
FROM TableA

UNION

SELECT
	  ID
	, 'Second_Value'		AS Type
	, Second_Value		AS Values
FROM TableA
2

Hi BabyAqua,

I think this is the simplest way. :slight_smile:

Regards,
gigawatt38
MCSA: SQL 2016 Database Development
MCSE: Data Management and Analytics

1 Like
3

Please note union will give you distinct rows. If you want to return duplicate rows if they exist in the two tables you can use UNION ALL. If duplicate rows are OK it will save the overhead of making the result set unique.

1 Like
4

You can do this with UNPIVOT:

Declare @T Table
(
	Id int identity(1,1),
	First_Value Int,
	Second_Value Int
)

insert into @T values (55,10),(65,70)

select * from @t
unpivot (
	[values] for [type] in (First_Value,Second_Value)
)UPVT
1 Like
5

Thank you everyone!

Andy, again, very cool!