CODEWITHSUNDEEP
sql-server-2012
Cyber Slueth Omega
Cyber Slueth Omega

Reputation: 399

How to retrieve column name from the table and parse the results for each fieldname in sql 2012

How do I retrieve the column name of the table I am performing a select on? I'm trying to append the results to the previous results for each field name.

example ( does not work ):

declare @result as nvarchar(max)
concat(select distinct parse(@result, COLName(field1), N':' from sometable, field1))

sometable:

id,unit
`---------`
1,quart
2,gallon
3,liter

result

id: 1, 2, 3
unit: quart, gallon, liter

I'm actually trying to do this on every field in my table that has text, varchar, char where the size is less than size 4000 using the following select statement:

Actual list of fields for my table I'm trying to parse (This works):

select top 1000 SCHEMA_NAME([o].schema_id) as [Schema Name],
       [s].name as [Table Name],
       [c].name as [Field Name],
       [t].name as [Type],
       [c].max_length as [Length],
       [c].precision,
       [c].scale,
       [c].is_identity
from sys.sysobjects as [s]
inner join sys.all_columns as [c] on [s].id = [c].object_id
inner join sys.systypes as [t] on [c].system_type_id = [t].xtype 
inner join sys.objects as [o] on [s].id = [o].object_id
where [s].xtype like 'U' and [t].name not like 'sysname%' and [s].name=N'TableName'

That way I can clearly see the names of each field that has a distinct result set.

Upvotes: 0

Views: 81

Answers (1)

Alexander Sigachov
Alexander Sigachov

Reputation: 1551

If you know ahead the list of fiels, you can use something like:

SELECT
(
    SELECT cast(id as varchar(50)) + ','
    FROM sometable
    ORDER by id
    FOR XML PATH('')
) as list1

UNION ALL

SELECT
(
    SELECT unit + ','
    FROM sometable
    ORDER by id
    FOR XML PATH('')
) as list2

...

Upvotes: 1

Related Questions