jfreeman2010
asked on
t-sql pivot table issue
HI,
I created the following t-sql example code, the result from a PIVOT table
will needs to UNION ALL columns with #TMP_TB_1, the final result will have the following cols:
APPMT_ID, LAST_FIRST_NAME, + COLS OF SRV_NAME.... + DOB, SEX
I need help on how to UNION ALL columns from a PIVOT Table and other table. Thanks you.
CREATE TABLE #TMP_TB
(
APPMT_ID INT,
SRV_ID INT,
SRV_NAME VARCHAR(50)
)
CREATE TABLE #TMP_TB_1
(
APPMT_ID INT,
LAST_FIRST_NAME VARCHAR(50),
DOB VARCHAR(10),
SEX CHAR(1)
)
insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');
insert into #TMP_TB values(16862, 2, 'HP');
insert into #TMP_TB values(16862, 49, 'PSA');
insert into #TMP_TB values(16862, 136, 'A1c');
insert into #TMP_TB values(16261, 2, 'HP');
insert into #TMP_TB values(16866, 2, 'HP');
insert into #TMP_TB values(16866, 136, 'A1c');
insert into #TMP_TB values(16866, 281, 'Nicotine');
insert into #TMP_TB values(17005, 2, 'HP');
insert into #TMP_TB values(17005, 281, 'Nicotine');
insert into #TMP_TB values(16869, 2, 'HP');
insert into #TMP_TB values(16869, 49, 'PSA');
insert into #TMP_TB values(16869, 136, 'A1c');
insert into #TMP_TB values(14806, 2, 'HP');
insert into #TMP_TB values(14806, 133, 'TSH');
insert into #TMP_TB values(14806, 281, 'Nicotine');
insert into #TMP_TB values(16867, 2, 'HP');
insert into #TMP_TB values(16867, 133, 'TSH');
insert into #TMP_TB values(16867, 136, 'A1c');
insert into #TMP_TB values(16867, 281, 'Nicotine');
DECLARE @columns VARCHAR(max),
@query VARCHAR(max)
;
SELECT
@columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
'[' + cast(SRV_NAME as varchar)+ ']')
FROM #TMP_TB
GROUP BY SRV_NAME
;
SET @query = '
SELECT *
FROM #TMP_TB
PIVOT
(
MAX(SRV_ID)
FOR [SRV_NAME]
IN (' + @columns + ')
) X
'
EXECUTE(@query)
DROP TABLE #TMP_TB;
I created the following t-sql example code, the result from a PIVOT table
will needs to UNION ALL columns with #TMP_TB_1, the final result will have the following cols:
APPMT_ID, LAST_FIRST_NAME, + COLS OF SRV_NAME.... + DOB, SEX
I need help on how to UNION ALL columns from a PIVOT Table and other table. Thanks you.
CREATE TABLE #TMP_TB
(
APPMT_ID INT,
SRV_ID INT,
SRV_NAME VARCHAR(50)
)
CREATE TABLE #TMP_TB_1
(
APPMT_ID INT,
LAST_FIRST_NAME VARCHAR(50),
DOB VARCHAR(10),
SEX CHAR(1)
)
insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');
insert into #TMP_TB values(16862, 2, 'HP');
insert into #TMP_TB values(16862, 49, 'PSA');
insert into #TMP_TB values(16862, 136, 'A1c');
insert into #TMP_TB values(16261, 2, 'HP');
insert into #TMP_TB values(16866, 2, 'HP');
insert into #TMP_TB values(16866, 136, 'A1c');
insert into #TMP_TB values(16866, 281, 'Nicotine');
insert into #TMP_TB values(17005, 2, 'HP');
insert into #TMP_TB values(17005, 281, 'Nicotine');
insert into #TMP_TB values(16869, 2, 'HP');
insert into #TMP_TB values(16869, 49, 'PSA');
insert into #TMP_TB values(16869, 136, 'A1c');
insert into #TMP_TB values(14806, 2, 'HP');
insert into #TMP_TB values(14806, 133, 'TSH');
insert into #TMP_TB values(14806, 281, 'Nicotine');
insert into #TMP_TB values(16867, 2, 'HP');
insert into #TMP_TB values(16867, 133, 'TSH');
insert into #TMP_TB values(16867, 136, 'A1c');
insert into #TMP_TB values(16867, 281, 'Nicotine');
DECLARE @columns VARCHAR(max),
@query VARCHAR(max)
;
SELECT
@columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
'[' + cast(SRV_NAME as varchar)+ ']')
FROM #TMP_TB
GROUP BY SRV_NAME
;
SET @query = '
SELECT *
FROM #TMP_TB
PIVOT
(
MAX(SRV_ID)
FOR [SRV_NAME]
IN (' + @columns + ')
) X
'
EXECUTE(@query)
DROP TABLE #TMP_TB;
What help do you need?
ASKER
I need help on UNION ALL columns from the result of PIVOT Table and #TMP_TB_1 table. thanks,
You are using the term UNION ALL wrongly, and that was confusing me. I'm easily confused :-)
UNION ALL is combination of rows. What you want is the combination of columns, and that is a JOIN.
Please find below the updated part of your SET @query code:
You may also want at the end of the code
as well.
UNION ALL is combination of rows. What you want is the combination of columns, and that is a JOIN.
Please find below the updated part of your SET @query code:
SET @query = '
WITH myTable as (SELECT *
FROM #TMP_TB
PIVOT
(
MAX(SRV_ID)
FOR [SRV_NAME]
IN (' + @columns + ')
) X)
SELECT a.*, ' + @columns + ' FROM myTable
LEFT JOIN #TMP_TB_1 a
ON myTable.APPMT_ID = a.APPMT_ID
'
You may also want at the end of the code
DROP TABLE #TMP_TB_1;
as well.
ASKER
Hi Phillip,
Thank you very much for help. I try it and the result only shows the #TMP_TB_1 columns, not sure why. Any also can the combine result insert to a temp table? those result need to be further processing.
Thanks!!!
Thank you very much for help. I try it and the result only shows the #TMP_TB_1 columns, not sure why. Any also can the combine result insert to a temp table? those result need to be further processing.
Thanks!!!
I get this answer:
Here's the full code I've got:
APPMT_ID LAST_FIRST_NAME DOB SEX A1c HP Nicotine PSA TSH
----------- -------------------------------------------------- ---------- ---- ----------- ----------- ----------- ----------- -----------
14806 GO, Anna 1994-05-26 F NULL 2 281 NULL 133
16261 BR, Joe 1969-01-20 M NULL 2 NULL NULL NULL
16862 BN, John 1980-12-01 M 136 2 NULL 49 NULL
16866 CH, Mary 1974-03-05 F 136 2 281 NULL NULL
16867 JZ, Barbara 1984-09-13 F 136 2 281 NULL 133
16869 FL, Kent 1999-08-14 M 136 2 NULL 49 NULL
17005 ED, JoAnn 1985-10-11 F NULL 2 281 NULL NULL
Here's the full code I've got:
CREATE TABLE #TMP_TB
(
APPMT_ID INT,
SRV_ID INT,
SRV_NAME VARCHAR(50)
)
CREATE TABLE #TMP_TB_1
(
APPMT_ID INT,
LAST_FIRST_NAME VARCHAR(50),
DOB VARCHAR(10),
SEX CHAR(1)
)
insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');
insert into #TMP_TB values(16862, 2, 'HP');
insert into #TMP_TB values(16862, 49, 'PSA');
insert into #TMP_TB values(16862, 136, 'A1c');
insert into #TMP_TB values(16261, 2, 'HP');
insert into #TMP_TB values(16866, 2, 'HP');
insert into #TMP_TB values(16866, 136, 'A1c');
insert into #TMP_TB values(16866, 281, 'Nicotine');
insert into #TMP_TB values(17005, 2, 'HP');
insert into #TMP_TB values(17005, 281, 'Nicotine');
insert into #TMP_TB values(16869, 2, 'HP');
insert into #TMP_TB values(16869, 49, 'PSA');
insert into #TMP_TB values(16869, 136, 'A1c');
insert into #TMP_TB values(14806, 2, 'HP');
insert into #TMP_TB values(14806, 133, 'TSH');
insert into #TMP_TB values(14806, 281, 'Nicotine');
insert into #TMP_TB values(16867, 2, 'HP');
insert into #TMP_TB values(16867, 133, 'TSH');
insert into #TMP_TB values(16867, 136, 'A1c');
insert into #TMP_TB values(16867, 281, 'Nicotine');
DECLARE @columns VARCHAR(max),
@query VARCHAR(max)
;
SELECT
@columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
'[' + cast(SRV_NAME as varchar)+ ']')
FROM #TMP_TB
GROUP BY SRV_NAME
;
SET @query = '
WITH myTable as (SELECT *
FROM #TMP_TB
PIVOT
(
MAX(SRV_ID)
FOR [SRV_NAME]
IN (' + @columns + ')
) X)
SELECT a.*, ' + @columns + ' FROM myTable
LEFT JOIN #TMP_TB_1 a
ON myTable.APPMT_ID = a.APPMT_ID
'
EXECUTE(@query)
DROP TABLE #TMP_TB;
DROP TABLE #TMP_TB_1;
How about this:
This gives the following results:
CREATE TABLE #TMP_TB
(
APPMT_ID INT,
SRV_ID INT,
SRV_NAME VARCHAR(50)
)
CREATE TABLE #TMP_TB_1
(
APPMT_ID INT,
LAST_FIRST_NAME VARCHAR(50),
DOB VARCHAR(10),
SEX CHAR(1)
)
insert into #TMP_TB_1 values(16862, 'BN, John', '1980-12-01', 'M');
insert into #TMP_TB_1 values(16261, 'BR, Joe', '1969-01-20', 'M');
insert into #TMP_TB_1 values(16866, 'CH, Mary', '1974-03-05', 'F');
insert into #TMP_TB_1 values(17005, 'ED, JoAnn', '1985-10-11', 'F');
insert into #TMP_TB_1 values(16869, 'FL, Kent', '1999-08-14', 'M');
insert into #TMP_TB_1 values(14806, 'GO, Anna', '1994-05-26', 'F');
insert into #TMP_TB_1 values(16867, 'JZ, Barbara', '1984-09-13', 'F');
insert into #TMP_TB values(16862, 2, 'HP');
insert into #TMP_TB values(16862, 49, 'PSA');
insert into #TMP_TB values(16862, 136, 'A1c');
insert into #TMP_TB values(16261, 2, 'HP');
insert into #TMP_TB values(16866, 2, 'HP');
insert into #TMP_TB values(16866, 136, 'A1c');
insert into #TMP_TB values(16866, 281, 'Nicotine');
insert into #TMP_TB values(17005, 2, 'HP');
insert into #TMP_TB values(17005, 281, 'Nicotine');
insert into #TMP_TB values(16869, 2, 'HP');
insert into #TMP_TB values(16869, 49, 'PSA');
insert into #TMP_TB values(16869, 136, 'A1c');
insert into #TMP_TB values(14806, 2, 'HP');
insert into #TMP_TB values(14806, 133, 'TSH');
insert into #TMP_TB values(14806, 281, 'Nicotine');
insert into #TMP_TB values(16867, 2, 'HP');
insert into #TMP_TB values(16867, 133, 'TSH');
insert into #TMP_TB values(16867, 136, 'A1c');
insert into #TMP_TB values(16867, 281, 'Nicotine');
DECLARE @columns VARCHAR(max),
@query VARCHAR(max)
;
SELECT
@columns = COALESCE(@columns + ',[' + cast(SRV_NAME as varchar) + ']',
'[' + cast(SRV_NAME as varchar)+ ']')
FROM #TMP_TB
GROUP BY SRV_NAME
;
SET @query = '
WITH myTable as (SELECT *
FROM #TMP_TB
PIVOT
(
MAX(SRV_ID)
FOR [SRV_NAME]
IN (' + @columns + ')
) X)
SELECT a.*, ' + @columns + ' into TMP_TC FROM myTable
LEFT JOIN #TMP_TB_1 a
ON myTable.APPMT_ID = a.APPMT_ID
'
EXECUTE(@query)
select * from TMP_TC
DROP TABLE #TMP_TB;
DROP TABLE #TMP_TB_1
DROP TABLE TMP_TC;
This gives the following results:
APPMT_ID LAST_FIRST_NAME DOB SEX A1c HP Nicotine PSA TSH
----------- -------------------------------------------------- ---------- ---- ----------- ----------- ----------- ----------- -----------
14806 GO, Anna 1994-05-26 F NULL 2 281 NULL 133
16261 BR, Joe 1969-01-20 M NULL 2 NULL NULL NULL
16862 BN, John 1980-12-01 M 136 2 NULL 49 NULL
16866 CH, Mary 1974-03-05 F 136 2 281 NULL NULL
16867 JZ, Barbara 1984-09-13 F 136 2 281 NULL 133
16869 FL, Kent 1999-08-14 M 136 2 NULL 49 NULL
17005 ED, JoAnn 1985-10-11 F NULL 2 281 NULL NULL
ASKER
yes, I much missing some code. Thank you!!
The the result insert to a temp table? and how? Thank you!!
The the result insert to a temp table? and how? Thank you!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for helping!!!