How to Get Column Name Based on Row Value in SQL Server

How can I get column name based on row value in SQL Server?

Is this what are you looking for

SELECT TOP 1 *,
CASE WHEN 5.4 > ColZ
THEN 'ColZ'
WHEN 5.4 > ColY
THEN 'ColY'
WHEN 5.4 > ColX
THEN 'ColX'
END [Column]
FROM T
ORDER BY [Date] DESC;

SQL Server : return column names based on a record's value

You can also use Cross apply

SELECT Cname
FROM Tablename
CROSS apply (VALUES('Field1',Field1),
('Field2',Field2),
('Field3',Field3),
('Field4',Field4)) ca (cname, data)
WHERE data = 1

To work dynamically use this.

CREATE TABLE test
(
Field1 INT,
Field2 INT,
Field3 INT,
Field4 INT
)

INSERT INTO test
VALUES ( 1,0,0,1 )

DECLARE @collist VARCHAR(max)='',
@sql NVARCHAR(max)

SELECT @collist += '(''' + COLUMN_NAME + ''',' + COLUMN_NAME + '),'
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'test'
AND COLUMN_NAME LIKE 'Field%'
AND TABLE_SCHEMA = 'dbo'

SELECT @collist = LEFT(@collist, Len(@collist) - 1)

SET @sql ='
SELECT Cname
FROM test
CROSS apply (VALUES' + @collist
+ ') ca (cname, data)
WHERE data = 1 '

EXEC Sp_executesql
@sql

Get column names and data to rows in SQL

You can use cross apply:

select t.*
from employees e
cross apply (values
('empid', cast(empid as varchar(100))),
('name', name),
('sex', sex)
) t(attr, value)
where e.empid = 200

Presumably, empid is a number, so explicit casting is needed (otherwise sql server will try to cast the name and sex to numbers, which will fail).

Demo on DB Fiddle:


attr | value
:---- | :----
empid | 200
name | Kate
sex | F

How to Select Row values as another table's column names?

This is untested, however, this should get you what you are after:

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ' +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' ' + QUOTENAME(field_name)
FROM list_config
WHERE list_config.config_name = 'username'
AND list_config.visible = 'Y'
--ORDER BY ??? --without an ORDER BY the order of the columns will be random/unpredictable
FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,10,N'') + NCHAR(13) + NCHAR(10) +
N'FROM work_order;';
--PRINT @SQL; --your debugging best friend
EXEC sp_executesql @SQL;

If you need to pass parameters, then make sure you parametrise your sp_execute statement; do not inject the parameter values into your dynamic statement.

If you want to validate the the column names are real column names, you can use an EXISTS:

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ' +
STUFF((SELECT ',' + NCHAR(13) + NCHAR(10) +
N' ' + QUOTENAME(lc.field_name)
FROM list_config lc
WHERE lc.config_name = 'username'
AND lc.visible = 'Y'
AND EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.COLUMN_NAME = lc.field_name
AND C.TABLE_NAME = N'Work_order')
--ORDER BY ???
FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,10,N'') + NCHAR(13) + NCHAR(10) +
N'FROM work_order;';
--PRINT @SQL; --your debugging best friend
EXEC sp_executesql @SQL;

How this answer works is that it builds a list of all the columns in a "delimited" format. The real "magic", is therefore in FOR XML PATH, so we'll going to start there with the subquery.

FOR XML PATH basically does what is says on the tin, it turns the result set into XML. We can use this functionality to concatenate all the values from the result set from the table work_order. I prefix (this is important) every value with N',' + NCHAR(13) + NCHAR(10) + N' '. This might seem a bit strange (some only use ','), so I'll explain. the comma is the easy one, between each column name we need one of those. NCHAR(13) + NCHAR(10) is a carriage return (unicode character 13) and a line break (unicode character 10). Then we have some white space. I do this purely for formatting, as well formatted dynamic SQL is much easier to trouble shoot than poorly formatted.

Then we have STUFF. There STUFF is used to remove the first prefix for each value of field_name (this is why I said that it being the prefix is important). STUFF's second parameter (1 in this case) is the position to start a replacement at, and the 3rd is how many characters to replace (so, characters 1 - 10). This final parameter is what to replace those characters with (''). So that replaces the first prefix (N',' + NCHAR(13) + NCHAR(10) + N' ') with ''.

Return column name of max value in a row SQL Server

You can use apply and window functions:

select t.*, max_cols
from t cross apply
(select string_agg(colname, ';') as max_cols
from (select top (1) with ties v.colname
from (values ('Job1', t.job1), ('Job2', t.job2), . . .
) v(colname, job)
order by v.job desc
) v
) v;

That said, you should probably fix your data model. Having multiple columns with the same information is usually a sign of a deficient data model. Each name/job combination should be a separate row in another table.

To get column names from table having a particular value

-- Test table
declare @T table(ID int, Col1 varchar(10), Col2 int, Col3 bit, Col4 varchar(max))
insert into @T values (1, '123', 123, 1, 'Some text long text')

-- ID to get one row
declare @ID int
set @ID = 1

-- Value to search for
declare @Val varchar(10)
set @Val = '123'

select
Col.value('local-name(.)', 'varchar(max)') as ColName
from (select *
from @T
where ID = @ID
for xml path(''), type) as T(XMLCol)
cross apply
T.XMLCol.nodes('*') as n(Col)
where Col.value('.', 'varchar(10)') = @Val

Result

ColName
--------
Col1
Col2


Related Topics



Leave a reply



Submit