Solved

SQL Pivot Table

Posted on 2009-04-02
7
815 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 50 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

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

617 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