Rows into column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rows into column

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-08 : 05:49:48

Sample data


DROP TABLE #testTable
CREATE TABLE #testTable ( id int, Data varchar(100) )

INSERT INTO #testTable 
SELECT 1, '12345'
UNION ALL
SELECT 2, 'Microsoft Windows XP Professional'
UNION ALL
SELECT 3, 'Service Pack 3'
UNION ALL
SELECT 4, '5/8/2010 0:4:37'
UNION ALL
SELECT 5, 'b4b9471c-1a5e-4d9c-94ef-84b00592946a.100'

SELECT * FROM #testTable

I have a resultset
I want these rows of id as column and data as value of the columns
column names will be RegID, OS, SP, Date, UpdateID for 1, 2, 3, 4, 5 respectively

How can i do it is the part of a large process.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 06:17:10

whats the column by which you group these rows? what designates which rows should be merged as a single record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-08 : 06:35:47

The resultset will have single entry for each id there will be no grouping.
as this result set is derived from some query

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 06:49:18

consider sample data

INSERT INTO #testTable
SELECT 1, '12345'
UNION ALL
SELECT 2, 'Microsoft Windows XP Professional'
UNION ALL
SELECT 3, 'Service Pack 3'
UNION ALL
SELECT 4, '5/8/2010 0:4:37'
UNION ALL
SELECT 5, 'b4b9471c-1a5e-4d9c-94ef-84b00592946a.100'
UNION ALL
SELECT 6, '21324'
UNION ALL
SELECT 7, 'Microsoft Windows 2000'
UNION ALL
SELECT 8, 'Service Pack 4'

then how will you determine which all should come in a row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-08 : 07:04:56

Might be i was not able to clear the thing
but the situation is like I will have one string in a loop i m spliting that string and getting fixed number of rows every time by my split function
SELECT id, Data From Split(@String, ',')
Now i m getting some resultset but now i want data as column values not the row values.
I hope now i m clear to you??
Please help me out..

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-08 : 07:49:20

Guys i got the solution..

SELECT
*
FROM
(
SELECT
CASE ID
WHEN 1 THEN 'RegID'
WHEN 2 THEN 'OS'
WHEN 3 THEN 'SP'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'UpdateID'
END AS Col,
Data FROM #TestTable
) AS src
PIVOT
(
MAX(Data) FOR Col IN ([RegID],[OS],[SP],[Date],[UpdateID])
) AS pvt

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 10:34:28

does you always have only one set of values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-10 : 02:14:52

Yes as I am separating a long string into multiple string and that each string having one set of values that which i m getting after again saperating values from each string.
i m getting which is like the sample data.

In First string strings are separated by #@@# and second string is seperated by RegID.

quote:
Originally posted by visakh16

does you always have only one set of values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:36:33

i cant really understand reason of doing all this!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources