locked
Return Column Names Where Values Match RRS feed

  • Question

  • Hi All,

    Running SQL Server 2008 R2.  DDL and Sample Data:

    CREATE TABLE [dbo].[Test]
        (
        [TestID]    [varchar](50) NOT NULL,
        [ParentID]  [varchar](50) NULL,
        [Value1]    [varchar](50) NULL,
        [Value2]    [varchar](50) NULL,
        [Value3]    [varchar](50) NULL,
        [Value4]    [varchar](50) NULL,
        [Value5]    [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
            )
        ON [PRIMARY]
        )
    ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Test]
        ([ID], [ParentID], [Value1], [Value2], [Value3], [Value4], [Value5])
    VALUES
        ('1', '1', 'A', 'B', 'C', 'D', 'E'),
        ('2', '1', 'A', 'B', 'D', 'D', 'C'),
        ('3', '1', 'A', 'C', 'B', 'D', 'E'),
        ('4', '2', 'A', 'E', 'C', 'B', 'D')

    I would like to return a list of column names based on a given ParentID, where each row's value in the column matches.  So, the matching query (based on a ParentID of 1) would give me the following column names:

    [Value1], [Value4]

    Whereas the non-matching query would return:

    [Value2], [Value3], [Value5]

    Any assistance is greatly appreciated!

    Best Regards

    Brad

    Wednesday, August 31, 2016 3:40 AM

Answers

  • That blog posts also shows Dynamic UNPIVOT although this is not what I meant either - I want to find my latest TechNet article

    http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

    and I think this article of mine can also be useful for you

    http://social.technet.microsoft.com/wiki/contents/articles/24169.sql-server-searching-all-columns-in-a-table-for-a-string.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, August 31, 2016 5:10 AM
  • Naomi's suggestion is a good one.  (You need to correct the typo - she has TYPE, you want PATH instead). 

    Another way to do it that doesn't involve doubling the quotes is to create a temp table with one column, modify the code I gave you that generates multiple rows with one column to insert the result into that temp table, then use the FOR XML method to convert those rows into a comma delimited list.  Then the non matching query would look like

    Declare @ColumnList nvarchar(max),
       @SQL nvarchar(max);
    
    Select @ColumnList = Stuff((Select ',(''' + c.name + ''', QuoteName(' + c.name + '))'
    From sys.columns c
    Inner Join sys.objects o On c.object_id = o.object_id
    Inner Join sys.schemas s On o.schema_id = s.schema_id
    Where s.name = 'dbo' And o.name = 'Test' And c.name Not In ('TestID', 'ParentID')
    Order By c.name
    For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)'),1,1,N'')
    
    Create Table #ListOfColumns(cols varchar(50))
    Set @SQL = 
    ';with mycte as (
    select [TestID], [ParentID], cols, vals from  [dbo].[Test]
    cross apply (values' + @ColumnList + ' )d(cols,vals)
    )
    insert #ListOfColumns(cols)
      select cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)>1)'
    
    Exec sp_executesql @SQL
    
    Select Stuff((Select ', ' + cols 
    From #ListOfColumns
    For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)'), 1,2, '') as Columns
    
    Drop Table #ListOfColumns;
    

    Make the same change to get the matching query. 

    Tom

    Thursday, September 1, 2016 2:17 AM

All replies

  • CREATE TABLE [dbo].[Test]
        (
        [TestID]    [varchar](50) NOT NULL,
        [ParentID]  [varchar](50) NULL,
        [Value1]    [varchar](50) NULL,
        [Value2]    [varchar](50) NULL,
        [Value3]    [varchar](50) NULL,
        [Value4]    [varchar](50) NULL,
        [Value5]    [varchar](50) NULL,
        CONSTRAINT [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
        WITH
            (
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
            )
        ON [PRIMARY]
        )
    ON [PRIMARY]
    GO
    
    INSERT INTO [dbo].[Test]
        ([TestID], [ParentID], [Value1], [Value2], [Value3], [Value4], [Value5])
    VALUES
        ('1', '1', 'A', 'B', 'C', 'D', 'E'),
        ('2', '1', 'A', 'B', 'D', 'D', 'C'),
        ('3', '1', 'A', 'C', 'B', 'D', 'E'),
        ('4', '2', 'A', 'E', 'C', 'B', 'D')
    ;with mycte as (
    select [TestID], [ParentID], cols, vals from  [dbo].[Test]
    cross apply (values ('value1',value1),('value2',value2),('value3',value3),('value4',value4),('value5',value5) )d(cols,vals)
    )
    
    select cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)=1)
    
    
      ;with mycte as (
    select [TestID], [ParentID], cols, vals from  [dbo].[Test]
    cross apply (values ('value1',value1),('value2',value2),('value3',value3),('value4',value4),('value5',value5) )d(cols,vals)
    )
      select cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)>1)
    
    	drop TABLE [dbo].[Test]

    • Proposed as answer by Naomi N Wednesday, August 31, 2016 5:03 AM
    Wednesday, August 31, 2016 4:05 AM
  • Hi Jingyang,

    Thanks for the quick reply.  Is there a way to make the above query dynamic?  There are many more columns in the actual table than in the sample I provided, and I would like the solution to be able to handle more columns being added.

    Thanks!

    Wednesday, August 31, 2016 4:08 AM
  • Are the number of columns likely to change with time? If not, a dynamic query isn't worth the effort
    Wednesday, August 31, 2016 4:26 AM
  • Hi Ryan, Yes, they are likely to change (new ones added).
    Wednesday, August 31, 2016 4:39 AM
  • Sure, simply use dynamic SQL with dynamic UNPIVOT and the same idea as Jingyang.

    I think I showed dynamic UNPIVOT in either some of my articles or forum posts, but I don't recall where exactly right now. I suggest to search for 'dynamic unpivot'.

    While I was writing the last sentence I remembered where I could have done it - let me find that article of mine.

    Hmm, the Google search didn't bring that article but brought this which should be enough to get you started (I'll continue looking for the article in the meantime):

    https://social.msdn.microsoft.com/Forums/SqlServer/en-US/beafb6f3-f162-4424-bec8-bd6bd6280459/dynamic-unpivot-with-unknown-number-of-rowscolumns?forum=transactsql


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, August 31, 2016 5:07 AM
  • That blog posts also shows Dynamic UNPIVOT although this is not what I meant either - I want to find my latest TechNet article

    http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

    and I think this article of mine can also be useful for you

    http://social.technet.microsoft.com/wiki/contents/articles/24169.sql-server-searching-all-columns-in-a-table-for-a-string.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, August 31, 2016 5:10 AM
  • Using Jingyang's method with the list of values created dynamically

    Declare @ColumnList nvarchar(max),
       @SQL nvarchar(max);
    
    Select @ColumnList = Stuff((Select ',(''' + c.name + ''', QuoteName(' + c.name + '))'
    From sys.columns c
    Inner Join sys.objects o On c.object_id = o.object_id
    Inner Join sys.schemas s On o.schema_id = s.schema_id
    Where s.name = 'dbo' And o.name = 'Test' And c.name Not In ('TestID', 'ParentID')
    Order By c.name
    For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)'),1,1,N'')
    
    
    Set @SQL = 
    ';with mycte as (
    select [TestID], [ParentID], cols, vals from  [dbo].[Test]
    cross apply (values' + @ColumnList + ' )d(cols,vals)
    )
    
    select cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)=1)'
    
    Exec sp_executesql @SQL
    
    
    Set @SQL = 
    ';with mycte as (
    select [TestID], [ParentID], cols, vals from  [dbo].[Test]
    cross apply (values' + @ColumnList + ' )d(cols,vals)
    )
      select cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)>1)'
    
    Exec sp_executesql @SQL
    
    Tom
    Wednesday, August 31, 2016 5:49 AM
  • Hi Tom,

    Thanks for the reply, just testing out your example and it's got me on the right track.  Currently, the CTE returns a multi-row resultset, whereas I'd like to return a comma-separated list of values.  I could use the STUFF function to achieve this, but I'm unsure of how to integrate it into a dynamically executed query.  Thoughts?

    Wednesday, August 31, 2016 7:22 PM
  • The same way you would normally do, just add 

    select stuff((select ', ' + cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)>1)
    
    FOR XML PATH('')), 1,2, '') as Columns

    Double single quote when you'll add it into dynamic query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, September 1, 2016 3:32 AM
    Wednesday, August 31, 2016 8:02 PM
  • Naomi's suggestion is a good one.  (You need to correct the typo - she has TYPE, you want PATH instead). 

    Another way to do it that doesn't involve doubling the quotes is to create a temp table with one column, modify the code I gave you that generates multiple rows with one column to insert the result into that temp table, then use the FOR XML method to convert those rows into a comma delimited list.  Then the non matching query would look like

    Declare @ColumnList nvarchar(max),
       @SQL nvarchar(max);
    
    Select @ColumnList = Stuff((Select ',(''' + c.name + ''', QuoteName(' + c.name + '))'
    From sys.columns c
    Inner Join sys.objects o On c.object_id = o.object_id
    Inner Join sys.schemas s On o.schema_id = s.schema_id
    Where s.name = 'dbo' And o.name = 'Test' And c.name Not In ('TestID', 'ParentID')
    Order By c.name
    For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)'),1,1,N'')
    
    Create Table #ListOfColumns(cols varchar(50))
    Set @SQL = 
    ';with mycte as (
    select [TestID], [ParentID], cols, vals from  [dbo].[Test]
    cross apply (values' + @ColumnList + ' )d(cols,vals)
    )
    insert #ListOfColumns(cols)
      select cols from mycte
    where  parentid=1
     group by cols
      having(count(distinct vals)>1)'
    
    Exec sp_executesql @SQL
    
    Select Stuff((Select ', ' + cols 
    From #ListOfColumns
    For XML Path(''),Type)
        .value('text()[1]','nvarchar(max)'), 1,2, '') as Columns
    
    Drop Table #ListOfColumns;
    

    Make the same change to get the matching query. 

    Tom

    Thursday, September 1, 2016 2:17 AM