Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
SQL Pivot Table
Is this possible in SQL
TABLE
Product | QT | Client
name1 | 2 | ClientName1
name1 | 5 | ClientName2
name2 | 3 | ClientName2
name2 | 7 | ClientName1
...
Result
Product ClientName1 ClientName2 ... SUM
name1 2 5 7
name2 7 3 10
I dont tink so but....
Tanks in advanced
TABLE
Product | QT | Client
name1 | 2 | ClientName1
name1 | 5 | ClientName2
name2 | 3 | ClientName2
name2 | 7 | ClientName1
...
Result
Product ClientName1 ClientName2 ... SUM
name1 2 5 7
name2 7 3 10
I dont tink so but....
Tanks in advanced
Asked:
2
2-
2
1 Solution
This is a cross tab report and any reporting tool will do it for you. SQL obviously isn't a good language for report development because it has no features for controling the presentation of data.
I should add that some SQL DBMSs have proprietary features that do this. Microsoft SQL Server has PIVOT for example.
I use SQL server 2005 and Lowfatspread i dont know how many clients there are. I would like an dynamic query.
try this then...
select Product,clientid,sum(qt) as qt
into #temp
from yourtable
where ...
group by product,clientid
Select client,identity(int,1,1) as rowno
into #temp1
from (
select distinct client
from #temp as t
) as x
declare @cid int
declare @sql varchar(8000)
set @cid = 1
set @sql='Select product'
while exists (select rowno from #temp1 where rowno=@cid)
do
select @SQL=@SQL+',SUm(Case when client='''+Client+''' then qt else null end) as ['+client+']'
,@cid=@rowno+1
from #temp1
Where rowno=@cid
end
Set @sql=@sql+',sum(qt) as [sum] from #temp group by product order by 1'
Exec(@SQL)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
select product
,sum(case when client = 'ClientName1' then qt else null end) as clientname1
,sum(case when client = 'ClientName2' then qt else null end) as clientname2
from yourtable
group by product
order by 1