Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: How can I get columnnames against each record in a table

    Hi,

    I have a table with almost 100 columns. Ex:

    ID ColA ColB ColC
    2 Null Word Excel

    3 Access Paint Null

    Expected result set is :

    ID ColA ColB ColC ColumnNames IsEmpty

    2 Null Word
    Excel ColA
    Yes

    2 Null Word
    Excel ColB
    No

    2 Null Word
    Excel ColC
    No
    3 Access Paint Null ColA
    No

    3 Access Paint Null ColB
    No
    3 Access Paint Null ColC
    Yes

    I would like to list all the column names against each record of the table and and would like to keep track whether the column is empty or nonempty. How can I write SQL query for this?

    Thanks

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Your "output" column headings are all on one line. The data is spread over multiple lines

    Suggest you post your input and desired output using the "Code" tag (the large # above the reply window..

  3. #3
    Join Date
    Jun 2011
    Posts
    11
    Thanks for the reply. The results should be in different columns.
    We can get partial results from the query pasted below,

    Select a.*, b.COLUMN_NAME from dbo.TestTable a
    join INFORMATION_SCHEMA.COLUMNS b
    on b.TABLE_NAME = 'TestTable'

    Then again, one more column need to be fetched, which gives information whether the data (Data refers to the data in the column equal to Column_Name of each row) is null or not for each row of the above resultset.
    Welcome for all ideas !!!

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by hmai View Post
    Then again, one more column need to be fetched, which gives information whether the data (Data refers to the data in the column equal to Column_Name of each row) is null or not for each row of the above resultset.
    Welcome for all ideas !!!
    You can use the code snippet below to provide table, column, and values. Modify as needed.
    Code:
    DECLARE @TableName varchar(50)
    DECLARE @ColumnName varchar(50)
    DECLARE @DataType varchar(50)
    CREATE TABLE #TMP(TableName varchar(50),ColumnName varchar(50),SQL varchar(500),AttributeValue varchar(50) NULL)
    
    DECLARE E_Cursor CURSOR FOR
     -- OPTIONAL: Use t.name to get the datatype for the attribute
    	SELECT o.Name as TableName,c.Name as ColumnName,t.name as DataType
    	FROM EMDDB.dbo.sysobjects o
    	INNER JOIN EMDDB.dbo.syscolumns c on c.id = o.id
    	INNER JOIN EMDDB.dbo.systypes t on t.xtype = c.xtype
    	WHERE o.xtype = 'U' 
    	AND t.name != 'sysname'
    	order by o.Name,c.Name     
    OPEN E_Cursor;
    FETCH NEXT FROM E_Cursor
    INTO @TableName,@ColumnName,@DataType;
    WHILE @@FETCH_STATUS = 0
       BEGIN
    		DECLARE @SQL NVARCHAR(500)
    		DECLARE @update_SQL NVARCHAR(500)
    		
    		SET @SQL = N' SELECT TOP 1 [' + @ColumnName + '] FROM [' + @TableName + '] ORDER BY 1 DESC ' 
    		INSERT INTO #TMP VALUES(@TableName,@ColumnName,@SQL,NULL)
    		
    		SET @update_SQL = N'UPDATE #TMP SET AttributeValue = (' + (SELECT SQL FROM #TMP WHERE [TableName] = @TableName AND [ColumnName] = @ColumnName)  + ') FROM #TMP T '
    		+ 'WHERE T.TableName = "' + @TableName + '" AND T.ColumnName = "' + @ColumnName + '"'
    		EXECUTE sp_executesql @update_SQL
    		PRINT @update_SQL
    		
    		FETCH NEXT FROM E_Cursor
    		INTO @TableName,@ColumnName,@DataType;
       END;
       SELECT * FROM #TMP
       DROP TABLE #TMP
       
    CLOSE E_Cursor;
    DEALLOCATE E_Cursor;
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •