Solved

SQL - select

Posted on 2015-02-23
3
57 Views
Last Modified: 2015-03-09
I want to list all productname for each column. A list of productname is on table2. There are about 50 of them.
Instead of using case (50 times or more) like below, is there a better way of query it?


select

tb1.producttype,
case productname when "productname1" then tbl2.shipped * quantity as productname1 else 0 end) productname1,
case productname when "productname2" then tbl2.shipped * quantity as productname1 else 0 end) productname2,
case productname when "productname3" then tbl2.shipped * quantity as productname1 else 0 end) productname3,
...
case productname = "productname50" then tbl2.shipped * quantity as productname1 else 0 end) productname50,
tb1.shippedto,
tbl1.Customername


from table1 tb1
inner join table2 tbl2
on tb1.id = tbl2.id
0
Comment
Question by:VBdotnet2005
[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
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40627248
You could consider using PIVOT instead of the case expression approach

BUT (in my opinion)

the case expression approach can do stuff rather easily that is very hard to achieve using PIVOT

The very best way of proceeding is for you to supply some "sample data" and an "expected result"

nb:
sample might only be 4 columns, not 50, and just a couple of rows.
a good example of this is shown at the link provided above.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40627258
when pivoting data using SQL, the column names (bold) are "hard coded"

select
tb1.producttype,
case productname when 'ABC123' then tbl2.shipped * quantity else 0 end) ABC123,
case productname when '123XYZ' then tbl2.shipped * quantity else 0 end) 123XYZ,
case productname when 'BLAH17' then tbl2.shipped * quantity else 0 end) BLAH17,
...
case productname = 'ETCETC50' then tbl2.shipped * quantity else 0 end) ETCETC50,

But you could concatenate them into a query from variables using C#
Otherwise you would need "dynamic sql"

This comment is true with the PIVOT operator and/or case expression approaches.
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40627960
Why not use a GROUP BY clause?
select 
 tb1.producttype,
productname,
 SUM(tbl2.shipped * quantity),
 tb1.shippedto,
 tbl1.Customername
 from table1 tb1
 inner join table2 tbl2
 on tb1.id = tbl2.id
group by  tb1.producttype, productname

Open in new window

0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server how to create a DYNAMIC TABLE? 11 47
Data Analysis 7 58
What is GIS method of Geometry data type? 6 29
When are cursors useful? 8 56
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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