Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rows Value into Columns Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 3:16 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:03 AM
Points: 62, Visits: 745
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

  Post Attachments 
Acutal Data.PNG (26 views, 21.92 KB)
Required Data.PNG (25 views, 20.81 KB)
Post #1418830
Posted Tuesday, April 30, 2013 11:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
Hi. Have you tried the pivot function?
Post #1448157
Posted Tuesday, April 30, 2013 11:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 839, Visits: 5,425
You could also do this in Reporting Services with a Matrix...
Post #1448308
Posted Wednesday, May 1, 2013 10:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 19, 2014 3:52 PM
Points: 364, Visits: 385
This is a classic cross-tab or pivot query.

The hardcoded way:
http://www.sqlservercentral.com/articles/T-SQL/63681/

The dynamic way:
http://www.sqlservercentral.com/articles/Crosstab/65048/
Post #1448685
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse