• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 833
  • Last Modified:

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
0
rop65828
Asked:
rop65828
  • 2
  • 2
  • 2
1 Solution
 
LowfatspreadCommented:
yes it is possible

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
0
 
dportasCommented:
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.
0
 
dportasCommented:
I should add that some SQL DBMSs have proprietary features that do this. Microsoft SQL Server has PIVOT for example.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rop65828Author Commented:
I use SQL server 2005 and Lowfatspread i dont know how many clients there are. I would like an dynamic query.
0
 
rop65828Author Commented:
Microsoft SQL Server 2005
0
 
LowfatspreadCommented:
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)

Open in new window

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now