0

This is my pivot statement

SELECT *
FROM testing.dbo.LC2
    PIVOT
    (
    MAX(Data2)
    FOR Key2
    IN ([Building Per $100 Of Limit Of Ins], [Business Personal Prop Per $100 Of Limit Of Ins], [Lessors Liability Per $100 Of Limit Of Ins], 
[Occ Liability Per $1,000 Of Annual Gross Sales], [Occupant Liability Per $1,000 Of Annual Payroll], [Occupant Liability Per $100 of Limit Of Ins])) AS PivotTable

I can perform a Select distinct on the column "Key2" and get the values that are seen in the "IN" part of the pivot:

SELECT DISTINCT Key2 FROM testing.dbo.LC2

+-------------------------------------------------+
| Key2                                            |
+-------------------------------------------------+
| Building Per $100 Of Limit Of Ins               |
+-------------------------------------------------+
| Business Personal Prop Per $100 Of Limit Of Ins |
+-------------------------------------------------+
| Lessors Liability Per $100 Of Limit Of Ins      |
+-------------------------------------------------+
| Occ Liability Per $1,000 Of Annual Gross Sales  |
+-------------------------------------------------+
| Occupant Liability Per $1,000 Of Annual Payroll |
+-------------------------------------------------+
| Occupant Liability Per $100 of Limit Of Ins     |
+-------------------------------------------------+

What I'd like to do is something like combine that "Select Distinct" values and insert them into the pivot statement. So I don't have to hardcode the values into the pivot statement. Something like this:

SELECT *
FROM testing.dbo.LC2
    PIVOT
    (
    MAX(Data2)
    FOR Key2
    IN (SELECT DISTINCT Key2 FROM testing.dbo.LC2)) AS PivotTable

Although, that doesn't work, how would I accomplish this?

BlakeB9
  • 345
  • 1
  • 3
  • 13
  • 1
    Please provide sample data and desired results so its clear what you are trying to accomplish. – Dale K Feb 11 '22 at 21:27
  • 1
    I already have the desired result. I'm just trying to know how to do it dynamically? I think that's the word for it. So that I don't have to hardcode the values into the pivot statement. – BlakeB9 Feb 11 '22 at 23:45
  • To do it dynamically use [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) – Squirrel Feb 12 '22 at 02:10

0 Answers0