SQL Server:Return Column Names Based on a Record's Value

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 

Return column names based on which holds the maximum value in the record

You could use UNPIVOT and get TOP 3 for each ID

;with temp AS
(
    SELECT ID, ColValue, ColName
    FROM @SampleData sd
    UNPIVOT
    (
       ColValue For ColName in ([colA], [colB], [colC], [colD], [colE])
    ) unp
)
SELECT sd.ID, ca.ColMax
FROM @SampleData sd
CROSS APPLY
(
    SELECT STUFF(
              (
                SELECT TOP 3 WITH TIES
                       ',' + t.ColName
                FROM temp t
                WHERE t.ID = sd.ID
                ORDER BY t.ColValue DESC
                FOR XML PATH('')
              )
             ,1,1,'') AS ColMax
) ca

See demo here: http://rextester.com/CZCPU51785

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;

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

How to retrieve a column name from a table that is stored as a value in another table

i add the [Last Refreshed] column to my tables and write this query and give me the correct answer

DROP TABLE  IF EXISTS #DB_DUMMY

CREATE TABLE  #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

DECLARE @COMMAND NVARCHAR(MAX)

SELECT @COMMAND = STRING_AGG(' INSERT INTO #DB_DUMMY SELECT DISTINCT '+CHAR(39)+T.name+CHAR(39)+',['+C.name+'] FROM '+S.name+'.'+T.name + ' GO', CHAR(13)+CHAR(10))
FROM sys.all_columns C
INNER JOIN sys.tables T ON C.object_id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = 'Last Refreshed'

PRINT(@COMMAND)

EXEC(@COMMAND)

SELECT * FROM #DB_DUMMY

two first line with IF EXISTS is new syntax in sql server 2017

Select column names and top 1 records along, dynamically

So the way this works is basically creates a bunch of selects like:

SELECT 'dbo' AS [Schema Name]
     , 'Table1' AS [Table Name]
     , 'Id' AS [Column Name]
     , 'bigint' AS [Data Type]
     , (SELECT TOP 1 CONVERT(NVARCHAR(MAX), Id) FROM [dbo].[Table1]) AS [Top 1 Value] 

UNION ALL

-- Another table

Values are converted into NVARCHAR(MAX) because column type in an union has to match and I guess that's the best bet.

Here goes:

DECLARE @query NVARCHAR(MAX) = ''
SELECT @Query += 
'SELECT ' + '''' + sch.name + '''' + ' AS [Schema Name],' + CHAR(13)+CHAR(10)
          + '''' + st.name  + '''' + ' AS [Table Name],' + CHAR(13)+CHAR(10)
          + '''' + c.name   + '''' + ' AS [Column Name],' + CHAR(13)+CHAR(10)
          + '''' + t.name   + '''' + ' AS [Data Type],' + CHAR(13)+CHAR(10)
          + '(SELECT TOP 1 CONVERT(NVARCHAR(MAX), ' + c.name + ') FROM ' + QUOTENAME(sch.name) + '.' + QUOTENAME(st.name) + ') AS [Top 1 Value] ' + CHAR(13)+CHAR(10)
          + 'UNION ALL'+CHAR(13)+CHAR(10)
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id 
JOIN sys.tables st ON st.object_id = i.object_id 
JOIN sys.schemas sch ON sch.schema_id = st.schema_id

-- Get rid of trailing UNION ALL
SET @Query = LEFT(@Query, LEN(@Query) - LEN('UNION ALLxx'))

PRINT @query
EXEC sp_executesql @query

Consider running with TOP 10 or some such first to make sure it's producing the right results.

---

Related Topics