Solved

How to reorganize an sql query?

Posted on 2013-12-09
4
119 Views
Last Modified: 2013-12-14
HI

I have a query that gives me the following information:

Code, ProductName, Size
  1       Blouse              S
  2       Blouse              S
  3       Skirt                  M
  4       Pants                L

The idea is to reorganize the information like this:

ProductName, SizeS, SizeM, SizeL
 Blouse               2         0         0
 Skirt                   0         1         0
 Pants                 0         0         1

Any ideas?

Regards.
0
Comment
Question by:dimensionav
  • 3
4 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39706184
select productname,
sum (case when Size = 'S' then 1 else 0 end) SizeS
sum (case when Size = 'M' then 1 else 0 end) SizeM
sum (case when Size = 'L' then 1 else 0 end) SizeL
from <Table>
group by productName
0
 
LVL 35

Expert Comment

by:YZlat
ID: 39706199
you might want to use pivot query

try something like this:

SELECT * FROM (SELECT Title,Code,'Size'+Size as sz FROM Table_1) Data
PIVOT (COUNT(Code)
FOR sz IN([SizeS],[sizeM],[SizeL])) AS CountSize
0
 
LVL 35

Expert Comment

by:YZlat
ID: 39706232
ged325, you are missing some commas
0
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 39706237
oops, Title should be ProductName:

SELECT * FROM (SELECT ProductName,Code,'Size'+Size as sz FROM Table_1) Data
PIVOT (COUNT(Code)
FOR sz IN([SizeS],[sizeM],[SizeL])) AS CountSize 

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

9 Experts available now in Live!

Get 1:1 Help Now