Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.
Course Of The Month
2 days, 13 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
Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| mask value when navigating | 5 | 24 | |
| I am new to using JSON in SQL Server | 2 | 59 | |
| ODBC failure when running MSACCESS query a second time. | 5 | 58 | |
| ErrorKind in crystal reprot VB.Net | 1 | 19 |
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
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
Course of the Month2 days, 13 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.