combine two colums into one columns using sql server script

Question

hi i have one doubt in sql server
how to combine two columns data into in to one columns and identification filed with values

CREATE TABLE [dbo].[empsal](
[eid] [int] NULL,
[salfrequence] varchar NULL,
[salary] [money] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[empsal] ([eid], [salfrequence], [salary]) VALUES (1, N'monthly', 100.0000)
GO
INSERT [dbo].[empsal] ([eid], [salfrequence], [salary]) VALUES (2, N'quartely', 300.0000)
GO
INSERT [dbo].[empsal] ([eid], [salfrequence], [salary]) VALUES (3, N'years', 200000.0000)
GO

based on above data i want out put like below :
eid |salfrequence |typeofmode
1 |100.00 |payvalue
1 |monthly |paytype
2 |300.00 |payvalue
2 |quartely |paytype
3 |200000.00 |payvalue
3 |years |paytype

I tried like below :

select eid,salfrequence ,'paytype' typeofmode from empsal
union
select eid,cast(salary as varchar(50)) ,'payvalue'typeofmode from empsal

its give correctly values.i want get same data without using union and union all concept

can you please tell me how to write query to achive this task in sql servr .

Answer 1

Try this query:

select eid, salfrequence, typeofmode
from (
   select eid, salfrequence as paytype, cast(salary as varchar(50)) as payvalue
   from empsal ) as s
unpivot
(
    salfrequence for typeofmode in (paytype, payvalue)
) as u
order by eid, typeofmode desc

Answer 2

Check this approach too:

select 
    eid, 
    choose(x, salfrequence, cast(salary as varchar(50))) as salfrequence,
    typeofmode
from empsal
cross apply (values (1, 'paytype'), (2, 'payvalue')) t(x, typeofmode)
order by eid, typeofmode desc

Answer 3

SELECT CAST([eid] AS varchar(10)) + ' |' + CAST([salary] AS varchar(10)) + ' |payvalue' AS [eid |salfrequence |typeofmode]
FROM [dbo].[empsal]
UNION ALL
SELECT CAST([eid] AS varchar(10)) + ' |' + [salfrequence] + ' |paytype' AS [eid |salfrequence |typeofmode]
FROM [dbo].[empsal]
ORDER BY [eid |salfrequence |typeofmode];

Answer 4

Hi @balakrishna k ,

Adding some notes to other experts' answers.

The conversion of columns into rows is called an UNPIVOT. You didn't specify what version of SQL Server you are using but there are several different ways to get the result.

1. You could use SELECT with UNION ALL as you provided.
2. If you are using SQL Server 2005+, then you could use the UNPIVOT function as provided by Viorel's first answer.
3. Instead of the UNPIVOT function you could also use CROSS APPLY with either VALUES (2008+) as provided by Viorel's second answer or UNION ALL as provided by Guoxiong's answer.

Please note that we need to cast the salary column to a varchar. This is because the datatype/length (in unpivot) of the columns must be the same since you will be transforming them into a single column in the final result.

In addition, CROSS APPLY actually seems to be more flexible and IMO more readable than using UNPIVOT.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.