SELECT --DISTINCT
ShiptoID = ro.ConsdUID, -- We don't need the ShiptoID, but we can't get rid of the column name either..
Name = LEFT(MAX(ro.Dba_Name), 35),
LicName = LEFT(MAX(ro.LicName), 35),
Addr1 = MAX(ro.Addr1),
City = MAX(ro.City),
State = MAX(ro.State),
Zipcode = MAX(ro.Zipcode),
LAT = MAX(ro.LAT),
LON = MAX(ro.LON),
-- This is the column I am trying to get all 3 outputs on 1 line???? @Container = COALESCE ( '
(CASE WHEN pl.container = 'Can' THEN 1 ELSE 0 END) AS Cans +
(CASE WHEN pl.container = 'Bottle' THEN 1 ELSE 0 END) AS Bottles +
(CASE WHEN pl.container = 'Keg' THEN 1 ELSE 0 END) AS Kegs '),
LastUpdate = GETDATE () --@Today
-- select top 100 rs.*
FROM RAD.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 RAD.dbo.vw_RtlOutletsNew ro --with (nolock)
ON rs.ConsdRAD = ro.ConsdRAD
I create a temp table that split the output of the container into 3 columns, cans, bottles and kegs. But I to all 3 values in the same column. At least on output.
I am sorry if this sounds rude. But If you want others to help you, please post sample data and expected output. We have no idea how your table looks like and what are the data in there. Not to mention your query joins several table. By posting just a query we simply does not have sufficient information to help you.