Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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