InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:30:00 GMT20http://www.sqlservercentral.com/Forums/Topic1418830-145-1.aspxThis is a classic cross-tab or pivot query.The hardcoded way:[url=http://www.sqlservercentral.com/articles/T-SQL/63681/]http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]The dynamic way:[url=http://www.sqlservercentral.com/articles/Crosstab/65048/]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]Wed, 01 May 2013 22:58:42 GMTMansfieldhttp://www.sqlservercentral.com/Forums/Topic1418830-145-1.aspxYou could also do this in Reporting Services with a Matrix...Tue, 30 Apr 2013 23:10:22 GMTpietlindenhttp://www.sqlservercentral.com/Forums/Topic1418830-145-1.aspxHi. Have you tried the pivot function?Tue, 30 Apr 2013 11:21:21 GMTmmartin1http://www.sqlservercentral.com/Forums/Topic1418830-145-1.aspxhi 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 ,[code="other"]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_NameINTO #RMC_BDCBPW_ReturnTableTRUNCATE TABLE #RMC_BDCBPW_ReturnTableINSERT 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_NameWAITFOR DELAY '00:00:00.700'GO 10-- Execute the Query for Ten timesSELECT * FROM #RMC_BDCBPW_ReturnTableSELECT 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 = 1UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 1) AS Tab1INNER JOIN(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 2) AS Tab2ON Tab2.RowNo = Tab1.RowNoINNER JOIN(SELECT 1 AS RowNo ,Gate1_Name AS FieldName FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 2 AS RowNo ,Gate2_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 3 AS RowNo ,Gate3_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 4 AS RowNo ,Gate4_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3UNION SELECT 5 AS RowNo ,Gate5_Name FROM #RMC_BDCBPW_ReturnTable WITH (NOLOCK) WHERE RowIndex = 3) AS Tab3ON Tab3.RowNo = Tab1.RowNo--Etc....[/code]Output i need described in Attachment(s) ThanksTue, 12 Feb 2013 03:16:05 GMTpatelmohamad