October 28, 2019 at 6:46 pm
hello i have 2 table
declare @t table (id int ,name char(10))
declare @y table (id int,item char(10),price int)
insert into @t (id,name)values (1,'A'),(2,'B')
insert into @y (id ,item,price) values (1,'red',2.00),(1,'black',3.00),(2,'red',6.00)
select * from @t
select * from @y
id name
----------- ----------
1 A
2 B
id item price
----------- ---------- -----------
1 red 2
1 black 3
2 red 6i want to give this result from query
id name red black
1 A 2 3
2 B 6 0
note: item column from @y table will add dynamically
October 28, 2019 at 7:50 pm
Looks like you want a Dynamic Pivot. Have you had a go at this at all, or at least tried to pivot it without dynamic SQL? You'll learn a lot more by trying. Or perhaps you have had a go, and can't make it dynamic? How far have you got?
Jeff did a great couple of articles on Cross tabs, years ago (which you can find here: https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1). Might be worth a read first, so you at least understand any answers you get.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 28, 2019 at 7:56 pm
Simple join on the ID columns, use sum and case for the amounts. Give it a try and post your code if you are still having trouble.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 28, 2019 at 8:33 pm
Simple join on the ID columns, use sum and case for the amounts. Give it a try and post your code if you are still having trouble.
I don't think this is sufficient, based on the "item column from @y table will add dynamically" comment.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2019 at 9:07 pm
below86 wrote:Simple join on the ID columns, use sum and case for the amounts. Give it a try and post your code if you are still having trouble.
I don't think this is sufficient, based on the "item column from @y table will add dynamically" comment.
I didn't scroll down far enough to see that. Then Thom A's suggestion of a pivot would be the way to go. I hadn't seen Thom's response before I posted mine.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 28, 2019 at 9:31 pm
Not dynamic, but if you know the values that will appear in item then:
select a.name,
sum(case when b.item = 'red' then b.price else 0 end) red,
sum(case when b.item = 'black' then b.price else 0 end) black
from @t a
inner join @y b
on a.id = b.id
group by a.name
October 28, 2019 at 10:04 pm
This is the dynamic approach. note that because the variables (@t and @y) would only be accessible in the scope outside of the dynamic SQL, I have had to use temporary tables to do this. You won't be table to use table variables unless that are defined as user-defined table-type variables, and then you will have to pass them as parameters to sp_executesql:
CREATE TABLE #t (id int,
name char(10));
CREATE TABLE #y (id int,
item char(10),
price int);
INSERT INTO #t (id,[name])
VALUES (1,'A'),
(2,'B');
INSERT INTO #y (id ,item,price)
VALUES (1,'red',2.00),
(1,'black',3.00),
(2,'red',6.00);
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT t.[name],' + @CRLF +
STUFF((SELECT N',' + @CRLF +
N' SUM(CASE y.item WHEN ' + QUOTENAME(y.item,'''') + N' THEN y.price ELSE 0 END) AS ' + QUOTENAME(y.item)
FROM #y y
GROUP BY y.item
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + @CRLF +
N'FROM #t t' + @CRLF +
N' JOIN #y y ON t.id = y.id' + @CRLF +
N'GROUP BY t.[name];';
PRINT @SQL; --Your best friend
EXEC sp_executesql @SQL;
DROP TABLE #t;
DROP TABLE #y;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply