As above mentioned. I have created 2 temp tables in SQL and need to insert vertically to complete all columns but as I understand, you can only insert data horizontally?
Is there a way around this?
Example :
ForgeinKey | Q1 | Q2 | Q3 | Q4 | Q5 |
1234 | 1 | 1| 0 | 2 | 0|
1235 | 0 | 2| 1 | 2 | 0|
1236 | 2 | 2| 0 | 0 | 1|
What I presume what would happen is something like this:
ForgeinKey | Q1 | Q2 | Q3 | Q4 | Q5 |
1234 | 1 |NULL | NULL | NULL| NULL|
1234 |NULL | 1 | NULL | NULL| NULL|
1234 |NULL |NULL | 0 | NULL| NULL|
1234 |NULL |NULL | NULL | 2 | NULL|
1234 |NULL |NULL | NULL | NULL| 0 | (etc)
Currently I have all data in the first couple of coloumns of my temp table(2) but after that I have 22 coloumns which I should populate with data from another temp table(1).
In temp table(1) I have all data displaying horizontally :
Answers | Questions | ForeignKey
1 | Q1 | 1234
1 | Q2 | 1234
0 | Q3 | 1234
2 | Q4 | 1234
0 | Q5 | 1234
As illustrated above, I need temp table(1) to populate temp table(2) vertically using the foreign key to identify rows:
ForgeinKey | Q1 | Q2 | Q3 | Q4 | Q5|
1234 | 1 | 1| 0 | 2| 0|
1235 | 0 | 2| 1 | 2| 0|
1236 | 2 | 2| 0 | 0| 1|
This is the code I am currently using with solution from bluefeet:
BEGIN
SET DATEFORMAT ymd;
--Temp Table 1 (Answers)
DECLARE @AnswersTempTable TABLE
(
tblManagerSpotCheckAnswer_ID bigint,
lManagerSpotCheck_ID bigint,
lManagersSpotCheckQuestion_ID bigint,
lManagersSpotCheckAnswer int,
sDescription varchar(100)
)
INSERT INTO @AnswersTempTable
SELECT tblManagerSpotCheckAnswers.tblManagerSpotCheckAnswer_ID, tblManagerSpotCheckAnswers.lManagerSpotCheck_ID,
tblManagerSpotCheckAnswers.lManagersSpotCheckQuestion_ID, tblManagerSpotCheckAnswers.lManagersSpotCheckAnswer,
tblManagerSpotCheckQuestions.sDescription
FROM tblManagerSpotCheckAnswers INNER JOIN
tblManagerSpotChecks ON tblManagerSpotCheckAnswers.lManagerSpotCheck_ID = tblManagerSpotChecks.lManagerSpotCheck_ID INNER JOIN
tblManagerSpotCheckQuestions ON
tblManagerSpotCheckAnswers.lManagersSpotCheckQuestion_ID = tblManagerSpotCheckQuestions.lManagersSpotCheckQuestion_ID LEFT OUTER JOIN
tblCallDiary ON tblManagerSpotChecks.lCallDiary_ID = tblCallDiary.lCallDiary_ID
WHERE (tblCallDiary.lCycle_ID = @lCycle_ID) AND (tblManagerSpotChecks.lRMStaff_ID = @Staff_ID)
ORDER BY tblManagerSpotChecks.lManagerSpotCheck_ID
--SELECT * FROM @AnswersTempTable
--Temp Table 2 (Results)
DECLARE @ResultTempTable TABLE
(
Completed_Date date,
Call_Date date,
Outlet varchar(100),
Channel_Manager varchar(100),
Area varchar(100),
TE varchar(100),
Spot_Checker varchar(100),
lChannel_ID bigint,
lManagerSpotCheck_ID bigint,
lCycle_ID int,
All_Handsets_displayed int,
All_Starter_Packs_displayed int,
Handset_stock_rotated int,
Starter_Pack_stock_rotated int,
All_Handsets_priced_correctly int,
All_Starter_Packs_priced_correctly int,
All_Handsets_Starter_Packs_Orders_Checked int,
Stock_order_placed int,
Minimum_Handset_stock_in_store int,
Minimum_Starter_Pack_stock_in_store int,
Latest_Deal_Generic_poster_on_display int,
Branding_as_per_Planogram int,
Sufficient_Contract_Forms int,
Sufficient_Brochures_in_store int,
Kiosk_functional int,
Cycle_Objective_Training int,
All_staff_trained_on_all_modules int,
Closed_group_training_session_booked int,
Outlet_is_able_to_RICA_8ta_starter_packs int,
All_OBF_Repairs_actioned int,
TE_Known_to_Store_Manager int,
Escalated_Relationship_issues int
)
INSERT INTO @ResultTempTable(Completed_Date, Call_Date, Outlet, Channel_Manager, Area, TE, Spot_Checker, lChannel_ID, lManagerSpotCheck_ID, lCycle_ID)
SELECT DISTINCT
tblManagerSpotChecks.dDateCaptured AS [Completed Date], tblManagerSpotChecks.dCallDate AS [Call Date], tblSites.sSitename AS Outlet,
tblChannel.sChannelManager AS [Channel Manager], PC_Location.sDescription AS Area, tblStaff_1.sNameSurname AS TE, tblStaff.sNameSurname AS [Spot Checker],
tblChannel.lChannel_ID, tblManagerSpotChecks.lManagerSpotCheck_ID, tblCallDiary.lCycle_ID
FROM tblCallDiary LEFT OUTER JOIN
tblStaff AS tblStaff_1 ON tblCallDiary.lStaff_ID = tblStaff_1.lStaff_ID RIGHT OUTER JOIN
tblChannel RIGHT OUTER JOIN
PC_Location RIGHT OUTER JOIN
tblSites ON PC_Location.lPC_Location_ID = tblSites.lPC_Location_ID ON tblChannel.lChannel_ID = tblSites.lChannel_ID RIGHT OUTER JOIN
tblManagerSpotCheckQuestions RIGHT OUTER JOIN
tblManagerSpotCheckAnswers RIGHT OUTER JOIN
tblStaff RIGHT OUTER JOIN
tblManagerSpotChecks ON tblStaff.lStaff_ID = tblManagerSpotChecks.lRMStaff_ID ON
tblManagerSpotCheckAnswers.lManagerSpotCheck_ID = tblManagerSpotChecks.lManagerSpotCheck_ID ON
tblManagerSpotCheckQuestions.lManagersSpotCheckQuestion_ID = tblManagerSpotCheckAnswers.lManagersSpotCheckQuestion_ID ON
tblSites.lSites_ID = tblManagerSpotChecks.lSite_ID ON tblCallDiary.lSite_ID = tblSites.lSites_ID
WHERE (tblManagerSpotChecks.dDateCaptured IS NOT NULL) AND (tblStaff.lStaff_ID = @Staff_ID) AND
(tblCallDiary.lCycle_ID = @lCycle_ID)
ORDER BY tblManagerSpotChecks.lManagerSpotCheck_ID
--SELECT * FROM @ResultTempTable
--SOLUTION FROM bluefeet
SELECT Completed_Date
,Call_Date
,Outlet
,Channel_Manager
,Area
,TE
,Spot_Checker
,lChannel_ID
,lManagerSpotCheck_ID
,lCycle_ID
,All_Handsets_displayed
,All_Starter_Packs_displayed
,Handset_stock_rotated
,Starter_Pack_stock_rotated
,All_Handsets_priced_correctly
,All_Starter_Packs_priced_correctly
,All_Handsets_Starter_Packs_Orders_Checked
,Stock_order_placed
,Minimum_Handset_stock_in_store
,Minimum_Starter_Pack_stock_in_store
,Latest_Deal_Generic_poster_on_display
,Branding_as_per_Planogram
,Sufficient_Contract_Forms
,Sufficient_Brochures_in_store
,Kiosk_functional
,Cycle_Objective_Training
,All_staff_trained_on_all_modules
,Closed_group_training_session_booked
,Outlet_is_able_to_RICA_8ta_starter_packs
,All_OBF_Repairs_actioned
,TE_Known_to_Store_Manager
,Escalated_Relationship_issues
FROM
(
SELECT tblManagerSpotCheckAnswer_ID
,lManagerSpotCheck_ID
,lManagersSpotCheckQuestion_ID
,lManagersSpotCheckAnswer
,sDescription
FROM @AnswersTempTable
) src
pivot
(
MAX(lManagersSpotCheckAnswer)
FOR lManagersSpotCheckQuestion_ID IN ( All_Handsets_displayed
,All_Starter_Packs_displayed
,Handset_stock_rotated
,Starter_Pack_stock_rotated
,All_Handsets_priced_correctly
,All_Starter_Packs_priced_correctly
,All_Handsets_Starter_Packs_Orders_Checked
,Stock_order_placed
,Minimum_Handset_stock_in_store
,Minimum_Starter_Pack_stock_in_store
,Latest_Deal_Generic_poster_on_display
,Branding_as_per_Planogram
,Sufficient_Contract_Forms
,Sufficient_Brochures_in_store
,Kiosk_functional
,Cycle_Objective_Training
,All_staff_trained_on_all_modules
,Closed_group_training_session_booked
,Outlet_is_able_to_RICA_8ta_starter_packs
,All_OBF_Repairs_actioned
,TE_Known_to_Store_Manager
,Escalated_Relationship_issues
)
)piv;
END
I still get errors when doing this.
Your question is not entirely clear but you can apply the UNPIVOT function to transform data from columns into rows or the PIVOT function to transform rows into columns.
Once the data gets in the format that you need you can INSERT it as needed.
PIVOT:
select ForeignKey, Q1, Q2, Q3, Q4, Q5
from
(
select ForeignKey, questions, answers
from table1
) src
pivot
(
max(answers)
for questions in (Q1, Q2, Q3, Q4, Q5)
) piv;
UNPIVOT:
select ForgeinKey, questions, answers
from table2
unpivot
(
answers
for questions in (Q1, Q2, Q3, Q4, Q5)
) unpiv
See SQL Fiddle with Demo of both versions