May 21, 2014 at 12:34 pm
I have 3 temp tables that I am pulling data from. The second and third tables may have a ProdID that is missing from the first table, but I need the first column in the output to show all ProdID's only once. If the columns are blank, then leave them null. The first table has an extra column the other 2 tables do not have.
I need the ProdID column to look like this for the output:
ProdID
1
2
3
4
5
and then fill in the other columns as appropriate. The attached SQLOutput is a screenshot of what I am looking for.
Here is some code I used to create the sample:
/* Create 3 sample tables and insert sample data */
--drop table #t1
CREATE TABLE #T1
(ProdID tinyint
,ProdDesc varchar(10)
,RecordCount_1 tinyint
,ExtraColumn varchar(20)
)
--DROP TABLE #T2
CREATE TABLE #T2
(ProdID tinyint
,ProdDesc varchar(10)
,RecordCount_2 tinyint
)
--DROP TABLE #T3
CREATE TABLE #T3
(ProdID tinyint
,ProdDesc varchar(10)
,RecordCount_3 tinyint
)
/* insert records into 3 temp tables */
INSERT INTO #T1
(ProdID,ProdDesc,RecordCount_1,ExtraColumn)
SELECT 1,'Prod 1', 11,'Extra Column' UNION ALL
SELECT 2,'Prod 2',12,'Extra Column' UNION ALL
SELECT 3,'Prod 3',13,'Extra Column'
--SELECT * FROM #T1
INSERT INTO #T2
(ProdID,ProdDesc,RecordCount_2)
SELECT 2,'Prod 2',22 UNION ALL
SELECT 3,'Prod 3', 23 UNION ALL
SELECT 5,'Prod 5',25
INSERT INTO #T3
(ProdID,ProdDesc,RecordCount_3)
SELECT 1,'Prod 1', 31 UNION ALL
SELECT 3,'Prod 3', 33 UNION ALL
SELECT 4,'Prod 4', 34 UNION ALL
SELECT 5,'Prod 5',35
I have no idea how to create the output. I tried outer joins, but that only works with the ProdID in T1. I tried a union all, but that gets the ProdID repeated.
Any ideas would be helpful.
Thank you!
May 21, 2014 at 2:19 pm
Nice job posting ddl and sample data in a consumable format. The biggest issue you have here is that you don't really have a base table that you can use to drive your query. We can however use a numbers or tally table to help us with this.
This code will produce the output as described. You can and should read up about tally tables. Here is an excellent link. http://www.sqlservercentral.com/articles/62867/[/url]
;with MinMax as
(
select MIN(MinProdID) as MinProdID, MAX(MaxProdID) as MaxProdID
from
(
select MIN(ProdID) as MinProdID, MAX(ProdID) as MaxProdID from #T1
union all
select MIN(ProdID), MAX(ProdID) from #T2
union all
select MIN(ProdID), MAX(ProdID) from #T3
) x
),
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N as ProdID,
coalesce(#t1.ProdDesc, #t2.ProdDesc, #t3.ProdDesc) as ProdDesc,
RecordCount_1,
RecordCount_2,
RecordCount_3,
ExtraColumn
from cteTally t
left join #T1 on #t1.ProdID = t.N
left join #T2 on #T2.ProdID = t.N --#T2.ProdID
left join #T3 on #T3.ProdID = t.N --#t1.ProdID
cross apply MinMax x
where t.N >= x.MinProdID
and t.N <= x.MaxProdID
order by N
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2014 at 8:57 am
Thank you! I was wondering if there was a way to do this without a totals table. Also, thank you for the link about the Tally table. I have never heard of that and will research it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply