Changes Row to Columns (Transpose) in Sql Server 2005 using COALESCE
Sample table as Cities in rows is convert to single column
State (Tablename) ----- City (Columnname) ----- Chennai Madurai Coimbatore
declare @retstr varchar(8000) select Top 5 @retstr = COALESCE(@retstr + ';','') + City from State print @retstrResult:
Chennai;Madurai;Coimbatore
Leave a reply
Comments
- yogesh says:
May 01, 09Please check this link to Transpose Columns to Rows in Sql Server 2005
- bill says:
Dec 11, 09I log SSIS package execution but want to transform the table into a xtab to allow calculation of stats. I have data like process event eventDt dayEvent timeEvent eoo start 2009-08-16 03:30:08.843 08/16/2009 03:30:08:843 eoo stop 2009-08-16 03:37:42.887 08/16/2009 03:37:42:887 eoo start 2009-08-18 03:30:12.907 08/18/2009 03:30:12:907 eoo stop 2009-08-18 03:57:55.090 08/18/2009 03:57:55:090 eoo start 2009-08-20 03:30:12.533 08/20/2009 03:30:12:533 eoo stop 2009-08-20 03:53:22.613 08/20/2009 03:53:22:613 but want it as process start stop eoo 2009-08-16 03:30:08.843 2009-08-16 03:37:42.887 Any suggestions?
- kakasaheb says:
Jul 16, 10Chivtya
- Jack says:
Oct 07, 11Great Thought Thanks works great
Related Posts
- Selecting Random Rows from a Table in SQL Server
- Useful SQL Server Management Studio Keyboard Shortcuts
- Case-sensitive check/search/match in T-SQL - Sql Server 2005
- Get AM/PM in Sql Server DateTime Format
- Replace/Remove Special Characters in Sql Server 2005/2008
- Create Linked Server in Sql Server 2005
- Alter/Change new Default Value of a Column in already created Table
- T-SQL to Disable/Enable Job in SQL Server 2005
- Invoke URL from Sql Server 2005 Stored Procedure
- T-SQL to change schema/owner name in Sql Server
- Delete Statement with inner join in Sql Server 2005
- Transpose Rows to Columns in Sql Server 2005 using PIVOT
- Transpose Columns to Rows in Sql Server 2005 using UNPIVOT
- Split Function in Sql Server to break Comma-Separated Strings into Table
- Delete duplicate rows on table with/without primary key in Sql Server 2005
- Sql server: Special Characters (%,[,]) in LIKE operator using ESCAPE keyword
- Pagination in Sql Server 2005
- Transact-SQL to get description of column in Sql Server 2005 using fn_listextendedproperty
- SQL to Grant EXECUTE permission to all Procedures and Functions
- Search text/string|Find keyword in Sql Server Function and Procedure
- Title case (Proper case) Function in Sql Server 2005
- Cursor For Converting Rows to Column in Sql Server
