Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Pivot Table

Posted on 2009-04-02
7
Medium Priority
?
823 Views
Last Modified: 2012-05-06
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
Comment
Question by:rop65828
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 24051507
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
 
LVL 22

Expert Comment

by:dportas
ID: 24051642
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
 
LVL 22

Expert Comment

by:dportas
ID: 24051656
I should add that some SQL DBMSs have proprietary features that do this. Microsoft SQL Server has PIVOT for example.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:rop65828
ID: 24051955
I use SQL server 2005 and Lowfatspread i dont know how many clients there are. I would like an dynamic query.
0
 

Author Comment

by:rop65828
ID: 24051964
Microsoft SQL Server 2005
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 200 total points
ID: 24052726
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

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we’ll look at how to deploy ProxySQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question