September 25, 2016 at 9:54 am
CREATE TABLE [dbo].[TestTbl1](
[Name] [varchar](50) NULL,
[col1] [int] NULL,
[col2] [int] NULL,
[col3] [int] NULL,
[col4] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTbl1] ([Name], [col1], [col2], [col3], [col4]) VALUES (N'FirstName', 1, 2, 3, 4)
INSERT [dbo].[TestTbl1] ([Name], [col1], [col2], [col3], [col4]) VALUES (N'LastName', 11, 22, 33, 44)
INSERT [dbo].[TestTbl1] ([Name], [col1], [col2], [col3], [col4]) VALUES (N'MiddleName', 10, 20, 30, 40)
USE [master]
GO
SELECT [Name]
,[col1]
,[col2]
,[col3]
,[col4]
FROM [Test].[dbo].[TestTbl1]
results:
Name col1 col2 col3 col4
FirstName 1 2 3 4
LastName 11 22 33 44
MiddleName 10 20 30 40
How do you get these results?
FirstName LasName
1 11
2 22
3 33
4 44
September 25, 2016 at 10:34 am
Quick suggestion
😎
;WITH FIRST_NAME_DATA AS
(
SELECT
FN.C_ID
,FN.C01
FROM dbo.TestTbl1 TT
CROSS APPLY
(
SELECT 1, col1 UNION ALL
SELECT 2, col2 UNION ALL
SELECT 3, col3 UNION ALL
SELECT 4, col4
) FN(C_ID,C01)
WHERE TT.Name = 'FirstName'
)
,LAST_NAME_DATA AS
(
SELECT
FN.C_ID
,FN.C01
FROM dbo.TestTbl1 TT
CROSS APPLY
(
SELECT 1, col1 UNION ALL
SELECT 2, col2 UNION ALL
SELECT 3, col3 UNION ALL
SELECT 4, col4
) FN(C_ID,C01)
WHERE TT.Name = 'LastName'
)
SELECT
FND.C01 AS FirstName
,LND.C01 AS LastName
FROM FIRST_NAME_DATA FND
INNER JOIN LAST_NAME_DATA LND
ON FND.C_ID = LND.C_ID;
Output
FirstName LastName
----------- -----------
1 11
2 22
3 33
4 44
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply