SQLTeam.com | Weblogs | Forums

Addition of 3 columns outputs into one line

tsql

#1

DECLARE @Container VARCHAR(20)

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


#2

need more info. some sample input and output would help


#3

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.


#4

it is continue from SELECT multiple column values

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.


#5

Here is the select from the table created:
ShipToID Name LicName Addr1 City State Zipcode Plus4 AreaCode Phone ClassOfTrade OnOffPremise LAT LON FMLYUID FamShort FamLong BRNDUID BrdShort BrdLong BBS Cans Bottles Kegs LastUpdate
107188 1 LIQUORS O M LIQUORS INC 130 HOWE AVE PASSAIC NJ 07055 2907 973 4581466 LIQUOR STORE Off-Premise 40.860970 -74.132440 21 SCH SCHAEFER 25 LGR LAGER 0.3629 1 0 0 2015-12-29 12:51:00
105920 1 REPUBLIK 221 WASHINGTON ST HOBOKEN NJ 07030 4716 201 8501281 RESTAURANT On-Premise 40.739000 -74.030340 74 STW SMALL TOWN 88 GNA NYF GINGER ALE 0.1452 0 1 0 2015-12-29 12:51:00
31675 1 STOP MARKET HANAA A ALKHATEEB 2315 S CENTRAL AVE LOS ANGELES CA 90011 1606 213 7476717 CONVENIENCE/ GAS Off-Premise 34.020810 -118.253960 2 PBR PABST 25 LGR LAGER 0.2904 1 0 0 2015-12-29 12:51:00
31675 1 STOP MARKET HANAA A ALKHATEEB 2315 S CENTRAL AVE LOS ANGELES CA 90011 1606 213 7476717 CONVENIENCE/ GAS Off-Premise 34.020810 -118.253960 2 PBR PABST 25 LGR LAGER 0.4839 0 1 0 2015-12-29 12:51:00
31675 1 STOP MARKET HANAA A ALKHATEEB 2315 S CENTRAL AVE LOS ANGELES CA 90011 1606 213 7476717 CONVENIENCE/ GAS Off-Premise 34.020810 -118.253960 22 SHM SCHLITZ ML 53 HGL HIGH GRAVITY LAGER 8.5% 0.1452 1 0 0 2015-12-29 12:51:00
31675 1 STOP MARKET HANAA A ALKHATEEB 2315 S CENTRAL AVE LOS ANGELES CA 90011 1606 213 7476717 CONVENIENCE/ GAS Off-Premise 34.020810 -118.253960 18 CLT COLT 45 34 MLQ MALT LIQUOR 0.9435 1 0 0 2015-12-29 12:51:00
31675 1 STOP MARKET HANAA A ALKHATEEB 2315 S CENTRAL AVE LOS ANGELES CA 90011 1606 213 7476717 CONVENIENCE/ GAS Off-Premise 34.020810 -118.253960 22 SHM SCHLITZ ML 35 MLQ MALT LIQUOR 0.2903 1 0 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 32 OLS OLD STYLE 25 LGR LAGER 0.4839 1 0 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 32 OLS OLD STYLE 25 LGR LAGER 0.3629 0 1 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 2 PBR PABST 25 LGR LAGER 0.7258 0 1 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 23 SHZ SCHLITZ 51 PRL PREMIUM LAGER 0.7742 1 0 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 74 STW SMALL TOWN 84 RTB NYFRB 0.7258 0 1 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 2 PBR PABST 25 LGR LAGER 22.1069 1 0 0 2015-12-29 12:51:00
145302 1000 LIQUORS MISKAS LIQUOR 1000 W BELMONT AVE CHICAGO IL 60657 3303 773 9351138 LIQUOR STORE Off-Premise 41.939910 -87.655250 24 SIM ST IDES ML 37 MLQ MALT LIQUOR 0.4838 0 1 0 2015-12-29 12:51:00


#6

Pretty hard to work with that. Please pos:

  1. CREATE TABLE statements
  2. INSERT INTO statements to populate the tables
  3. Expected results