• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Pivot T-SQL Coded. I want to join on results. How?

I've written T-SQL that pivots data.  Whats interesting is that the T-SQL dynamically generates the columns of the pivit based on values in the table being pivoted.  Everything works great but I want to used results to join to another table.  Ideally I would like to use this in a View or Tabled valued function but because the column results are dynamic I don't think I can do this.  Is there anyway I can create a view or function of some type with this SQL and use it in a join?
Thanks in advance,
Terry


--SELECT * FROM dbo.TcmViewEntityTaxId

DECLARE Cur CURSOR
FOR
    SELECT DISTINCT
            JurisdictionType
    FROM    TcmViewEntityTaxId
  
DECLARE @Temp NVARCHAR(MAX) ,
    @AllJurisdictionType NVARCHAR(MAX) ,
    @JurisdictionTypeQuery NVARCHAR(MAX)
    
SET @AllJurisdictionType = ''   
  
OPEN Cur
-- Getting all the subjects
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @AllJurisdictionType = @AllJurisdictionType + '[' + @Temp + '],'
        FETCH NEXT FROM Cur INTO @Temp
    END

CLOSE Cur
DEALLOCATE Cur

SET @AllJurisdictionType = SUBSTRING(@AllJurisdictionType, 0, LEN(@AllJurisdictionType))

SET @JurisdictionTypeQuery = 
'SELECT  EntityId , ' +
		@AllJurisdictionType +
'FROM    ( SELECT    EntityId ,
                    Number ,
                    JurisdictionType
          FROM      TcmViewEntityTaxId
        ) S PIVOT
 ( MAX(Number) FOR JurisdictionType IN (' + @AllJurisdictionType + ')) AS pvt'
 
 --PRINT @JurisdictionTypeQuery
 EXEC sp_executesql @JurisdictionTypeQuery

Open in new window

0
wahlster
Asked:
wahlster
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
What is the range of values in JurisdictionType? The name suggests this may be a finite list and as such mean the dynamic SQL is really unnecessary. Without the dynamic SQL, you can make the PIVOT into a view or JOIN to another table. If you absolutely need it this way, try to do the JOIN in the dynamic SQL.
0
 
wahlsterAuthor Commented:
Hi mwvisa1,
Thanks for the reply.  JurisdictionType is dynamic. If the column results are variable is there a way to get this T-SQL into a view/function/table valued function, stored procedure and do a join on it?  I was hoping to put it into some SQL construct that would allow others to join on the results.
Thanks,
Terry

0
 
Kevin CrossChief Technology OfficerCommented:
Since you do not know how many different JurisdictionType values you will have or their names to setup table ahead of time, you will probably have to try something like SELECT ... INTO SomeTableName ... You can then join on that table. The problem will be if it already exists, you will get an error. Alternative is to do the JOIN inside the dynamic SQL statement, so when you execute SQL you get both the PIVOT and JOIN at the same time.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now