;)
In this example, I am combining the data from three tables. Two of the tables have different columns and no relation or join.
I am managing a soccer team. I have a list of weeks in the soccer season. And I also have a list of players. In addition, I also have a list of weeks in which each player have confirmed.
DECLARE@WeeksTABLE
(
WeekIdintIDENTITYPRIMARYKEY
,WeekDateDATETIME
)
DECLARE@PlayersTABLE
(
PlayerIdintIDENTITYPRIMARYKEY
,PlayerNamevarchar(50)
)
DECLARE@ConfirmationsTABLE
(
ConfirmationIdintIDENTITYPRIMARYKEY
,PlayerIdint
,WeekIdint
)
Using Transact SQL, I want to create a roster that combines all of the players and all of the weeks and specifies whether a player has confirmed participation for that week.
-- declare result table
DECLARE@RosterTABLE
(
WeekIdint
,WeekDatedatetime
,PlayerIdint
,PlayerNamevarchar(50)
,IsConfirmedvarchar(3)
)To combine all Players and all Weeks, you simply perform a select without a join.
-- combine all weeks and all players to create roster
INSERTINTO@Roster
(
WeekId
,WeekDate
,PlayerId
,PlayerName
)
SELECT
w.WeekId
,w.WeekDate
,p.PlayerId
,p.PlayerName
FROM
@Weeksw
,@PlayerspOnce you have your roster, you can perform a quick update to add whether the player has confirmed for each of the weeks.
-- perform multiple update for confirmations'
UPDATE
@Roster
SET
IsConfirmed=CASEWHENc.PlayerIdISNOTNULLTHEN'Yes'ELSE'No'END
FROM
@Rosterr
[email protected]=c.PlayerIdANDr.WeekId=c.WeekIdAnd here is your roster! Let’s play some soccer. : )
