Rows Value into Columns

Question

Post reply

Rows Value into Columns

patelmohamad

SSCommitted

Points: 1873
More actions
February 12, 2013 at 3:16 am

#98581

hi Guys,
Need your help to sort out the Query, i have fix columns Field1 to Field10, and Variant Rows may be 1 ,5 ,10 , 20 etc...
i want the RowIndex as ColumnName
eg [1] ,[2] ,[3] ,[4] etc, and Respective Row and Gate1_Name(Column data) ,Gate2_Name ,Gate3_Name in RowIndex([1]) ,RowIndex([2]) ,RowIndex([3]) Column ,
IF NOT OBJECT_ID('tempdb..#RMC_BDCBPW_ReturnTable') IS NULL
DROP TABLE #RMC_BDCBPW_ReturnTable
SELECT
IDENTITY(BIGINT ,1 ,1) AS RowIndex
,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate1_Name
,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate2_Name
,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate3_Name
,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate4_Name
,CONVERT(VARCHAR(22) ,GETDATE() ,114) AS Gate5_Name
INTO #RMC_BDCBPW_ReturnTable
TRUNCATE TABLE #RMC_BDCBPW_ReturnTable
INSERT INTO #RMC_BDCBPW_ReturnTable(Gate1_Name ,Gate2_Name ,Gate3_Name ,Gate4_Name ,Gate5_Name)
SELECT
CONVERT( VARCHAR(20) ,GETDATE() + 1 ,121) AS Gate1_Name
,CONVERT( VARCHAR(20) ,GETDATE() + 2 ,121) AS Gate2_Name
,CONVERT( VARCHAR(20) ,GETDATE() + 3 ,121) AS Gate3_Name
,CONVERT( VARCHAR(20) ,GETDATE() + 4 ,121) AS Gate4_Name
,CONVERT( VARCHAR(20) ,GETDATE() + 5 ,121) AS Gate5_Name
WAITFOR DELAY '00:00:00.700'
GO 10
-- Execute the Query for Ten times
SELECT * FROM #RMC_BDCBPW_ReturnTable
SELECT
Tab1.FieldName AS [1]
,Tab2.FieldName AS [2]
,Tab3.FieldName AS [3]
FROM
(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1
UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1
UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1
UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1
UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1
) AS Tab1
INNER JOIN
(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2
UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2
UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2
UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2
UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2
) AS Tab2
ON Tab2.RowNo = Tab1.RowNo
INNER JOIN
(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3
UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3
UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3
UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3
UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3
) AS Tab3
ON Tab3.RowNo = Tab1.RowNo
--Etc....
Output i need described in Attachment(s)
Thanks
Patel Mohamad

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27764 More actions · Answer 1

Hi. Have you tried the pivot function?

----------------------------------------------------

pietlinden SSC Guru Points: 63421 More actions · Answer 2

You could also do this in Reporting Services with a Matrix...

Mansfield SSCrazy Points: 2366 More actions · Answer 3

This is a classic cross-tab or pivot query.

The hardcoded way:

http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]

The dynamic way:

http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]