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.
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
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.
- Databases
- Network Analysis
- Network Operations
- Network Security
- MySQL Server
- Network Architecture, Percona, *proxy servers, *Data Migration
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…
Suggested Courses
Course of the Month11 days, 7 hours left to enroll
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.