Convert column in multiple rows to a single csv column

Question

Convert column in multiple rows to a single csv column

Adam Bean

One Orange Chip

Points: 26631
More actions
October 26, 2011 at 1:25 pm

#250063

Hey all,
I need to return a single csv column for [sys].[dm_db_index_physical_stats]'s [alloc_unit_type_desc] and a bit unsure how to tackle this.
For example:
[script]
SELECT
[database_id]
,[object_id]
,[index_id]
,[partition_number]
,[alloc_unit_type_desc]
,[avg_fragmentation_in_percent]
,[page_count]
,[avg_page_space_used_in_percent]
,[record_count]
,[avg_record_size_in_bytes]
FROM [sys].[dm_db_index_physical_stats] (8, NULL, NULL , NULL, 'SAMPLED')
WHERE [object_id] = 1346103836
[/script]
This record returns three rows because of the [alloc_unit_type_desc]. Instead, I want a single record returned and the different values of [alloc_unit_type_desc] in a single csv column.
Thoughts?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

There is an easy and efficient method to do this using "FOR XML PATH". Do a search on XML path concatenation and you should find some good examples.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sleipner SSCommitted Points: 1664 More actions · Answer 2

First of all, you have more than one field causing duplication, in the example I found on my server all of the fields after alloc_unit_type_desc were also different, so you have to apply that to each field.

I'm sure the XML method, but I'm not as familiar with it. Another method is to use CTE's - this worked for me. If you want more than one row of output (i.e. look at >1 table at a time) you would need to use a cursor or a function.

declare @s1 varchar(max),@s2 varchar(max),@s3 varchar(max)

declare @s4 varchar(max),@s5 varchar(max),@s6 varchar(max)

SELECT @s1 = '',@s2 = '',@s3 = '',@s4 = '', @s5 = '', @s6 = ''

SELECT

@s1 = @s1 + [alloc_unit_type_desc] + ','

,@s2 = @s2 + cast([avg_fragmentation_in_percent] as varchar(20)) + ','

,@s3 = @s3 + cast([page_count] as varchar(20)) + ','

,@s4 = @s4 + cast([avg_page_space_used_in_percent] as varchar(20)) + ','

,@s5 = @s5 + cast([record_count] as varchar(20)) + ','

,@s6 = @s6 + cast([avg_record_size_in_bytes] as varchar(20)) + ','

FROM [sys].[dm_db_index_physical_stats] (8, NULL, NULL , NULL, 'SAMPLED')

WHERE [object_id] = 1346103836

SELECT [database_id]

,[object_id]

,[index_id]

,[partition_number]

,left(@s1,len(@s1)-1) [alloc_unit_type_desc]

,left(@s2,len(@s2)-1) [avg_fragmentation_in_percent]

,left(@s3,len(@s3)-1) [page_count]

,left(@s4,len(@s4)-1) [avg_page_space_used_in_percent]

,left(@s5,len(@s5)-1) [record_count]

,left(@s6,len(@s6)-1) [avg_record_size_in_bytes]

FROM [sys].[dm_db_index_physical_stats] (8, NULL, NULL , NULL, 'SAMPLED')

WHERE [object_id] = 1346103836

group by [database_id]

,[object_id]

,[index_id]

,[partition_number]

Mildofly SSChasing Mays Points: 625 More actions · Answer 3

This might help

SELECT

[database_id]

,[object_id]

,[index_id]

,[partition_number]

, [csv column] = substring((SELECT ( ', ' + [alloc_unit_type_desc] )

FROM [sys].[dm_db_index_physical_stats] (8, NULL, NULL , NULL, 'SAMPLED') as s2

WHERE s2.[database_id] = s1.[database_id] AND s2.[object_id] = s1.[object_id] AND s2.[index_id] = s1.[index_id]

FOR XML PATH( '' )

), 3, 1000 )

,[avg_fragmentation_in_percent]

,[page_count]

,[avg_page_space_used_in_percent]

,[record_count]

,[avg_record_size_in_bytes]

FROM

[sys].[dm_db_index_physical_stats] (8, NULL, NULL , NULL, 'SAMPLED') as s1

WHERE

[object_id] = 1346103836

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

CELKO (10/27/2011)
SQL Programmers do that kind of display formatting in the presentation layer and NEVER in the back end. Do you know what First Normal Form is (1NF) and why it is teh foundation of the Relational Model?
If you want to be a bad SQL programmer there are stinky kludges like XML, cursors, etc.

Not true if "the system" is creating files and/or there is no "front end". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Thanks for the ideas all. I actually ended up going a different route utilizing a temp table and performing updates/deletes based on the logic I needed and it met my needs. While this may have worked, the new way was a bit more clean.

Thanks again!