SQL Server - Can I dynamically create multiple parameters from the results of a SELECT query on another table?

Question

So I have my 'Chaplains' table:

CREATE TABLE Chaplaincy_Chaplains
(
ChaplainID INTEGER IDENTITY PRIMARY KEY,
ChaplainName VARCHAR(50),
FaithID INTEGER,
Telephone VARCHAR(50),
Email VARCHAR(255),
Notes VARCHAR(255),
FOREIGN KEY (FaithID) REFERENCES Chaplaincy_Faiths(FaithID)
);

Into which I have inserted four records:

INSERT INTO Chaplaincy_Chaplains
VALUES ('Robin Richardson',1,'555-123456','[email protected]',NULL)

INSERT INTO Chaplaincy_Chaplains
VALUES ('Peter Jackson',8,'555-123-456','[email protected]',NULL)

INSERT INTO Chaplaincy_Chaplains
VALUES ('Harry Davidson',1,'555-123-456','[email protected]',NULL)

INSERT INTO Chaplaincy_Chaplains
VALUES ('Steve Morrison',7,'555-123-456','[email protected]',NULL)

At the moment I have hard-coded those records into my 'Rota' query:

SELECT RotaDate,  DateDay, SlotTypeID, SlotDescription AS Hours,
    max(case when ChaplainName = 'Robin Richardson' then AvailabilityDescription end) Robin ,
    max(case when ChaplainName = 'Peter Jackson' then AvailabilityDescription end) Peter ,
    max(case when ChaplainName = 'Harry Davidson' then AvailabilityDescription end) Harry ,
    max(case when ChaplainName = 'Steve Morrison' then AvailabilityDescription end) Steve 
    FROM Chaplaincy_Rota,Chaplaincy_Availability, Chaplaincy_Chaplains, Chaplaincy_Dates, Chaplaincy_Faiths, Chaplaincy_SlotTypes
    WHERE
    RotaDate = Chaplaincy_Dates.Date AND
    RotaSlot = SlotTypeID AND
    RotaChaplain = ChaplainID AND
    RotaAvailability = AvailabilityID AND
    ChaplainFaith = Chaplaincy_Faiths.FaithID
    GROUP BY RotaDate, SlotTypeID, SlotDescription, DateDay
    ORDER BY RotaDate, SlotTypeID;

Which gives me something like this:

                                  | Robin  | Peter | Harry   | Steve   |

01/06/2015 | Mon | 00:00 to 08:00 | Leave  | Study | On-call | Avail   |

01/06/2015 | Mon | 08:00 to 13:00 | Leave  | Study | On-call | Avail   |

01/06/2015 | Mon | 13:00 to 18:00 | Leave  | Study | On-call | Avail   |

01/06/2015 | Mon | 18:00 to 00:00 | Leave  | Study | On-call | Avail   |

02/06/2015 | Tue | 00:00 to 08:00 | On-call| Avail | Study   | Avail   |

02/06/2015 | Tue | 08:00 to 13:00 | On-call| Avail | Study   | Avail   |

02/06/2015 | Tue | 13:00 to 18:00 | On-call| Avail | Avail   | Avail   |

02/06/2015 | Tue | 18:00 to 00:00 | On-call| Avail | Avail   | Avail   |
....
et cetera

Is there a way I can dynamically update my 'Rota' query, so that every time I add a new record to the 'Chaplains' table, that record will automatically be included in the 'Rota' query? In effect, I want to be able to add a column to the 'Rota' query, just by adding a row to the Chaplains table.

Many thanks for reading!

Yes there is way. Look for dynamic query and EXEC function — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Jun 1, 2015 at 12:29
You would either need to use dynamic SQL, or preferably do the pivot in your presentation layer. There are plenty of questions about on this - search for dynamic pivot (or search for bluefeet) — GarethD
– GarethD, Commented Jun 1, 2015 at 12:32

ThePravinDeshmukh · Accepted Answer · 2015-06-01 12:45:19Z

Can you check this

Declare @sql varchar(max) = ''
set @sql = 'SELECT RotaDate,  DateDay, SlotTypeID, SlotDescription AS Hours,'

select @sql = @sql + ' 
' + 'max(case when ChaplainName = '''+ChaplainName+''' then AvailabilityDescription end) '+LEFT(ChaplainName, CHARINDEX(' ', ChaplainName+ ' ') - 1)+' ,'
FROM Chaplaincy_Chaplains

SET @sql = LEFT(@sql, LEN(@sql) - 1)

SET @sql = @sql + ' ' +
    '
    FROM Chaplaincy_Rota,Chaplaincy_Availability, Chaplaincy_Chaplains, Chaplaincy_Dates, Chaplaincy_Faiths, Chaplaincy_SlotTypes
    WHERE
    RotaDate = Chaplaincy_Dates.Date AND
    RotaSlot = SlotTypeID AND
    RotaChaplain = ChaplainID AND
    RotaAvailability = AvailabilityID AND
    ChaplainFaith = Chaplaincy_Faiths.FaithID
    GROUP BY RotaDate, SlotTypeID, SlotDescription, DateDay
    ORDER BY RotaDate, SlotTypeID;'

Print @sql
EXEC (@sql)

OUTPUT :

    SELECT RotaDate,  DateDay, SlotTypeID, SlotDescription AS Hours, 
max(case when ChaplainName = 'Robin Richardson' then AvailabilityDescription end) Robin , 
max(case when ChaplainName = 'Peter Jackson' then AvailabilityDescription end) Peter , 
max(case when ChaplainName = 'Harry Davidson' then AvailabilityDescription end) Harry , 
max(case when ChaplainName = 'Steve Morrison' then AvailabilityDescription end) Steve  
    FROM Chaplaincy_Rota,Chaplaincy_Availability, Chaplaincy_Chaplains, Chaplaincy_Dates, Chaplaincy_Faiths, Chaplaincy_SlotTypes
    WHERE
    RotaDate = Chaplaincy_Dates.Date AND
    RotaSlot = SlotTypeID AND
    RotaChaplain = ChaplainID AND
    RotaAvailability = AvailabilityID AND
    ChaplainFaith = Chaplaincy_Faiths.FaithID
    GROUP BY RotaDate, SlotTypeID, SlotDescription, DateDay
    ORDER BY RotaDate, SlotTypeID;

Glad i could help, Please accept the answer if it worked so other's could know it too. meta.stackoverflow.com/questions/251078/…

Collectives™ on Stack Overflow

SQL Server - Can I dynamically create multiple parameters from the results of a SELECT query on another table?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related