SELECT multiple column values

Transact-SQL
1

CASE
WHEN pl.container = 'Cans' THEN 'Cans'
WHEN pl.Container = 'Bottles' THEN 'Bottles'
WHEN pl.container = 'Kegs' THEN 'Kegs'
ELSE 'FALSE'
END

2

What is your question?

3

In a SELECT statement or maybe needs to be in a Where clause, I need to find a way pull out of a column with multiple entries (3) and separate to another columns for distributers.

4

Show us sample data and expected output to make your question clear.

5

SELECT pl.BrdLong,
BBS = SUM(rs.TotalBBS),

	Cans =   CASE WHEN pl.container = 'Cans' THEN 'Cans'
	Bottles =	CASE WHEN pl.Container = 'Bottles' THEN 'Bottles'
	Kegs = 	CASE WHEN pl.container = 'Kegs' THEN 'Kegs'
	
	LastUpdate = GETDATE () --@Today
	-- select top 100 rs.*
FROM dbo.vw_RtlSalesNew rs --with (nolock) 
INNER JOIN MM_Data.dbo.vw_VBS_Prodlist_UXT pl --with (nolock) 
	ON pl.ProdUID = rs.ProdUID

WHERE rs.InvoiceDate >= '2015-12-01'

6

Sorry, But time to go.
I will over any suggestions later.
Thanks

7

you are looking for this ?

SELECT pl.BrdLong,
	--BBS = SUM(rs.TotalBBS),
	Cans = SUM(CASE 
			WHEN pl.container = 'Cans'
				THEN rs.TotalBBS
			END),
	Bottles = SUM(CASE 
			WHEN pl.Container = 'Bottles'
				THEN rs.TotalBBS
			END),
	Kegs = SUM(CASE 
			WHEN pl.container = 'Kegs'
				THEN rs.TotalBBS
			END)
--LastUpdate = GETDATE() --@Today
-- select top 100 rs.*
FROM dbo.vw_RtlSalesNew rs --with (nolock) 
INNER JOIN MM_Data.dbo.vw_VBS_Prodlist_UXT pl --with (nolock) 
	ON pl.ProdUID = rs.ProdUID
WHERE rs.InvoiceDate >= '2015-12-01'
GROUP BY pl.BrdLong
8

I don't think so. These 3 values from the container table have nothing to with totalBBS.

9

then please post some sample data and expected result.

10

Here is my insert into a temp table.

INSERT INTO #Mike_Loc_Tracker
(ShipToID, dba_Name, LicName, Addr1, City, State,
Zipcode, Plus4, AreaCode, Phone, ClassOfTrade, OnOffPremise,
LAT, LON, FMLYUID, FamShort, FamLong, BRNDUID, BrdShort, BrdLong, BBS, Cans, Bottles, Kegs, LastUpdate)

SELECT --DISTINCT
ShiptoID = ro.ConsdRAD, -- We don't need the ShiptoID, but we can't get rid of the column name either..
dba_Name = LEFT(MAX(rs.Dba_Name), 35),
LicName = LEFT(MAX(rs.LicName), 35),
Addr1 = MAX(ro.Addr1),
City = MAX(rs.City),
State = MAX(rs.State),
Zipcode = MAX(rs.Zipcode),
Plus4 = MAX(rs.Plus4),
AreaCode = MAX(ro.AreaCode),
Phone = MAX(ro.Phone),
ClassOfTrade = MAX(ro.ClassOfTrade),
OnOffPremise = MAX(ro.OnOffPremise),
LAT = MAX(rs.roLAT),
LON = MAX(rs.roLON),
fb.FMLYUID,
pl.FamShort,
pl.FamLong,
fb.BRNDUID,
pl.BrdShort,
pl.BrdLong,
BBS = SUM(rs.TotalBBS),
-- This where I am trying to split the container into 3 values:
Cans = CASE WHEN pl.container = 'Cans' --THEN 'Cans'
Bottles = CASE WHEN pl.Container = 'Bottles' --THEN 'Bottles'
Kegs = CASE WHEN pl.container = 'Kegs' --THEN 'Kegs'

	LastUpdate = GETDATE () --@Today
	-- select top 100 rs.*
FROM dbo.vw_RtlSalesNew rs --with (nolock) 
INNER JOIN MM_Data.dbo.vw_VBS_Prodlist_UXT pl --with (nolock) 
	ON pl.ProdUID = rs.ProdUID
INNER JOIN Pabst_DW1.dbo.prProducts p --with (nolock) 
	ON pl.ProdUID = p.ProdUID
INNER JOIN Pabst_DW1.dbo.prFamBrands fb --with (nolock) 
	ON p.FMBRUID = fb.FMBRUID
INNER JOIN dbo.vw_RtlOutletsNew ro --with (nolock) 
	ON rs.ConsdRAD = ro.ConsdRAD
WHERE rs.InvoiceDate >= '2015-12-01' --BETWEEN @StartDate AND @EndDate
	AND ro.CoTCode NOT IN('06','07','50','99','37','19') 	-- Omit Non-Retail accounts, Sub-Distributors, Employee Sales..	
GROUP BY ro.ConsdRAD, fb.FMLYUID, pl.FamShort, pl.FamLong, fb.BRNDUID, pl.BrdShort, pl.BrdLong
11

You still haven't posted sample data with expected output. We are unable to help with the information given so far.

12

Sorry, With the holidays and all, I didn't see this.

-- This where I am trying to split the container column into 3 columns with values of cans, bottles and kegs:
Cans = CASE WHEN pl.container = 'Cans' --THEN 'Cans'
Bottles = CASE WHEN pl.Container = 'Bottles' --THEN 'Bottles'
Kegs = CASE WHEN pl.container = 'Kegs' --THEN 'Kegs'

13

Since I have no idea what you are looking for, see if this helpsSELECT --DISTINCT ShiptoID = ro.ConsdRAD -- We don't need the ShiptoID, but we can't get rid of the column name either.. ,dba_Name = LEFT(MAX(rs.Dba_Name), 35) ,LicName = LEFT(MAX(rs.LicName), 35) ,Addr1 = MAX(ro.Addr1) ,City = MAX(rs.City) ,STATE = MAX(rs.STATE) ,Zipcode = MAX(rs.Zipcode) ,Plus4 = MAX(rs.Plus4) ,AreaCode = MAX(ro.AreaCode) ,Phone = MAX(ro.Phone) ,ClassOfTrade = MAX(ro.ClassOfTrade) ,OnOffPremise = MAX(ro.OnOffPremise) ,LAT = MAX(rs.roLAT) ,LON = MAX(rs.roLON) ,fb.FMLYUID ,pl.FamShort ,pl.FamLong ,fb.BRNDUID ,pl.BrdShort ,pl.BrdLong ,BBS = SUM(rs.TotalBBS) ,SUM(CASE WHEN pl.container = 'Cans' THEN 1 ELSE 0 END) AS CANS ,SUM(CASE WHEN pl.container = 'Bottles' THEN 1 ELSE 0 END) AS BOTTLES ,SUM(CASE WHEN pl.container = 'Kegs' THEN 1 ELSE 0 END) AS Kegs -- This where I am trying to split the container into 3 values: --Cans = CASE --WHEN pl.container = 'Cans' --THEN 'Cans' --Bottles = CASE --WHEN pl.Container = 'Bottles' --THEN 'Bottles' --Kegs = CASE --WHEN pl.container = 'Kegs' --THEN 'Kegs' ,LastUpdate = GETDATE() --@Today -- select top 100 rs.* FROM dbo.vw_RtlSalesNew rs INNER JOIN MM_Data.dbo.vw_VBS_Prodlist_UXT pl ON pl.ProdUID = rs.ProdUID INNER JOIN Pabst_DW1.dbo.prProducts p ON pl.ProdUID = p.ProdUID INNER JOIN Pabst_DW1.dbo.prFamBrands fb ON p.FMBRUID = fb.FMBRUID INNER JOIN dbo.vw_RtlOutletsNew ro ON rs.ConsdRAD = ro.ConsdRAD WHERE rs.InvoiceDate >= '2015-12-01' --BETWEEN @StartDate AND @EndDate AND ro.CoTCode NOT IN ('06','07','50','99','37','19') -- Omit Non-Retail accounts, Sub-Distributors, Employee Sales.. GROUP BY ro.ConsdRAD ,fb.FMLYUID ,pl.FamShort ,pl.FamLong ,fb.BRNDUID ,pl.BrdShort ,pl.BrdLong;

14

I need to figure out a way to get only the values of container to be either Cans, Bottles or Kegs and divide it the 3 values.
When I use the CASE statement, either with SUM or not the only value I get is O.
Not sure why.
Hopefully that makes it less cloudy.
,(CASE WHEN pl.container = 'Cans' THEN 1 END) AS CANS
,(CASE WHEN pl.container = 'Bottles' THEN 1 END) AS BOTTLES
,(CASE WHEN pl.container = 'Kegs' THEN 1 ELSE 0 END) AS Kegs

Output:
ShiptoID dba_Name LicName Addr1 City State Zipcode Plus4 AreaCode Phone ClassOfTrade OnOffPremise LAT LON FMLYUID FamShort FamLong BRNDUID BrdShort BrdLong BBS CANS BOTTLES Kegs LastUpdate
0 ZIPPYS ZION EN ROUTE PO BOX 238 YUKON UT 84790 9900 999 9999999 WHOLESALE CLUB On-Premise 47.953720 0.000000 2 PBR PABST 26 LGR LAGER 3624.3853 NULL NULL 0 2015-12-28 12:17:15.660

15

You missed the Else on two of your lines. Also you did not use the SUM command to count them.

As I stated earlier, we do not know what you are expecting to populate the column.

16

Not sure why I am not making my needs to vague, not trying too.
I would like your help.
Tried many ways with the SUM, with the ELSE and without:
Mission is Adding 3 values to a process I have already, written pulling data from another database and splitting it into values when the tables column (container) is either cans, bottles or kegs and need put into different columns with the same names for further processing.

17

I the join works and gives all three values:

SELECT TOP 100--DISTINCT
ShiptoID = ro.ConsdUID,
BBS = SUM(rs.TotalBBS),
pl.Container,
/,SUM(CASE WHEN pl.container = 'Cans' THEN 1 ELSe 0 END) AS CANS
,SUM(CASE WHEN pl.container = 'Bottles' THEN 1 ELSE 0 END) AS BOTTLES
,SUM(CASE WHEN pl.container = 'Kegs' THEN 1 ELSE 0 END) AS Kegs/
LastUpdate = GETDATE ()

0 678.3741 Can 2015-12-28 13:46:07.867
0 582.1179 Bottle 2015-12-28 13:46:07.867
0 14372.2352 Bottle 2015-12-28 13:46:07.867
0 164350.5813 Can 2015-12-28 13:46:07.867
0 21486.0000 Keg 2015-12-28 13:46:07.867

18

But when I uncomment the SUM(CASE...
I get 0 on all values.
ShiptoID BBS Container CANS BOTTLES Kegs LastUpdate
0 678.3741 Can 0 0 0 2015-12-28 13:50:10.720
0 582.1179 Bottle 0 0 0 2015-12-28 13:50:10.720
0 14372.2352 Bottle 0 0 0 2015-12-28 13:50:10.720
0 164350.5813 Can 0 0 0 2015-12-28 13:50:10.720
0 21486.0000 Keg 0 0 0 2015-12-28 13:50:10.720

19

Based on the data you have shown - I would expect to see 0's.

The data shows the container value as 'Can', 'Bottle', 'Keg' - but you are looking for 'Cans', 'Bottles', 'Kegs'. No matches are found so the returned SUM will be 0.

20

Please post sample data and expected output. This is the easiest way for others to understand your need.