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!
Course Of The Month
7 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Pivot Table
Posted on 2009-04-02
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
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
[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
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
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
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.
I should add that some SQL DBMSs have proprietary features that do this. Microsoft SQL Server has PIVOT for example.
I use SQL server 2005 and Lowfatspread i dont know how many clients there are. I would like an dynamic query.
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)
Featured Post
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…
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…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 23 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
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.