Solved

SQL Pivot Table

Posted on 2009-04-02
7
772 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Read about achieving the basic levels of HRIS security in the workplace.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now