none
Using the result of a subquery as column names in the main query

    Question

  • Hey,

    I'm using SQL Server 2005, and I need to pull off something that I'm not entirely sure is possible - I have three tables, one of which contains the names of Audits (Table A), another that contains the names of columns used in each audit (fkey'd back to the entry on Table A they belong to, with another field for what order that column is in it's audit) (Table B), and another that contains the actual data for the cells in each column (Table C), fkey'd to the column it belongs to and an ID to for what record it belongs to. I realize this isn't the most elegant way of accomplishing what I'm trying to, but theoretically a new Audit needs to be buildable on demand from a VB.NET app without constraint as far as number of columns go and creating new tables on the fly isn't an option.

    The problem I'm having is that I need to be able to (as a view, query, script) grab all the columns that will be used given an Audit from TableA, pull out all data results from TableC, but set them in columns with the columns in order by the Col_Order pulled from TableB.

    Structures
    -------------
    TableA: ID | Name | Active
    TableB: A_ID | ID | Col_Name | Flag | Col_Order (A_ID + Col_Order are unique)
    TableC: B_ID | Record_ID | Value (B_ID + Record_ID are unique)

    Friday, February 24, 2012 12:16 AM

Answers

  • This is relatively easy with dynamic PIVOT.

    create procedure GetAuditInfo (@AuditName varchar(20)) declare @AuditID int select @AuditID = ID from Audits where Name = @AuditName IF @AuditID IS NULL begin raiserror('Invalid Audit Name %s passed',16, 1, @AuditName) return - 100 end declare @Cols nvarchar(max), @SQL nvarchar(max) select @Cols = stuff((select ',' + quotename(Col_Name) from AuditColumns where A_ID = @AuditID ORDER BY Col_Order FOR XML PATH('')),1,1,'') IF NULLIF(@Cols,'') IS NULL begin raiserror ('No columns for the passed audit name found!',16,1) return -200 end set @SQL = 'SELECT * FROM (select B.Value, C.Col_Name from AuditData B inner join AuditColumns C ON B.B_ID = C.ID

    WHERE C.A_ID = @AuditID) src PIVOT (MAX(Value) FOR Col_Name IN (' + @Cols + ')) pvt' print @SQL -- for debug execute sp_ExecuteSQL @SQL, N'@AuditID int', @AuditID

    The above is from the top of my head, may need some tweaking.


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


    My blog


    • Edited by Naomi NEditor Friday, February 24, 2012 1:42 AM
    • Marked as answer by Xero_Mobius Friday, February 24, 2012 2:31 AM
    Friday, February 24, 2012 1:41 AM

All replies

  • This is relatively easy with dynamic PIVOT.

    create procedure GetAuditInfo (@AuditName varchar(20)) declare @AuditID int select @AuditID = ID from Audits where Name = @AuditName IF @AuditID IS NULL begin raiserror('Invalid Audit Name %s passed',16, 1, @AuditName) return - 100 end declare @Cols nvarchar(max), @SQL nvarchar(max) select @Cols = stuff((select ',' + quotename(Col_Name) from AuditColumns where A_ID = @AuditID ORDER BY Col_Order FOR XML PATH('')),1,1,'') IF NULLIF(@Cols,'') IS NULL begin raiserror ('No columns for the passed audit name found!',16,1) return -200 end set @SQL = 'SELECT * FROM (select B.Value, C.Col_Name from AuditData B inner join AuditColumns C ON B.B_ID = C.ID

    WHERE C.A_ID = @AuditID) src PIVOT (MAX(Value) FOR Col_Name IN (' + @Cols + ')) pvt' print @SQL -- for debug execute sp_ExecuteSQL @SQL, N'@AuditID int', @AuditID

    The above is from the top of my head, may need some tweaking.


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


    My blog


    • Edited by Naomi NEditor Friday, February 24, 2012 1:42 AM
    • Marked as answer by Xero_Mobius Friday, February 24, 2012 2:31 AM
    Friday, February 24, 2012 1:41 AM
  • This looks like it may well do the trick, and if not, it gives me a place to start where before I was completely lost.

    Thank you!

    Friday, February 24, 2012 2:32 AM