How to return concatenated value from sql query

Question

I'm querying a SQL Server database to return a concatenated value. Is it possible?

cnn <- odbcConnect("CN")
query = gsub("\n", " ", " DECLARE @Price VARCHAR(50) = ''
SELECT @Price =+ @Price + [Price] + ' + '
FROM [dbo].[wine]
SELECT @Price")
query

dataset1 <- sqlQuery(cnn, query)
dataset1

When I run the query in SSMS, it works. This is the error when dataset1 is run:

[1] "42S02 208 [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'dbo.wine'."
[2] "[RODBC] ERROR: Could not SQLExecDirect ' DECLARE @Price VARCHAR(50) = '' SELECT @Price =+ @Price + [Price] + ' + ' FROM [dbo].[wine] SELECT @Price'"

Code:

CREATE TABLE [dbo].[wine]
(
    [Year] [varchar](50) NULL,
    [Price] [varchar](50) NULL,
    [WinterRain] [varchar](50) NULL,
    [AGST] [varchar](50) NULL,
    [HarvestRain] [varchar](50) NULL,
    [Age] [varchar](50) NULL,
    [FrancePop] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1952', N'7.495', N'600', N'17.1167', N'160', N'31', N'43183.569')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1953', N'8.0393', N'690', N'16.7333', N'80', N'30', N'43495.03')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1955', N'7.6858', N'502', N'17.15', N'130', N'28', N'44217.857')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1957', N'6.9845', N'420', N'16.1333', N'110', N'26', N'45152.252')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1958', N'6.7772', N'582', N'16.4167', N'187', N'25', N'45653.805')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1959', N'8.0757', N'485', N'17.4833', N'187', N'24', N'46128.638')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1960', N'6.5188', N'763', N'16.4167', N'290', N'23', N'46583.995')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1961', N'8.4937', N'830', N'17.3333', N'38', N'22', N'47128.005')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1962', N'7.388', N'697', N'16.3', N'52', N'21', N'48088.673')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1963', N'6.7127', N'608', N'15.7167', N'155', N'20', N'48798.99')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1964', N'7.3094', N'402', N'17.2667', N'96', N'19', N'49356.943')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1965', N'6.2518', N'602', N'15.3667', N'267', N'18', N'49801.821')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1966', N'7.7443', N'819', N'16.5333', N'86', N'17', N'50254.966')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1967', N'6.8398', N'714', N'16.2333', N'118', N'16', N'50650.406')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1968', N'6.2435', N'610', N'16.2', N'292', N'15', N'51034.413')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1969', N'6.3459', N'575', N'16.55', N'244', N'14', N'51470.276')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1970', N'7.5883', N'622', N'16.6667', N'89', N'13', N'51918.389')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1971', N'7.1934', N'551', N'16.7667', N'112', N'12', N'52431.647')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1972', N'6.2049', N'536', N'14.9833', N'158', N'11', N'52894.183')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1973', N'6.6367', N'376', N'17.0667', N'123', N'10', N'53332.805')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1974', N'6.2941', N'574', N'16.3', N'184', N'9', N'53689.61')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1975', N'7.292', N'572', N'16.95', N'171', N'8', N'53955.042')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1976', N'7.1211', N'418', N'17.65', N'247', N'7', N'54159.049')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1977', N'6.2587', N'821', N'15.5833', N'87', N'6', N'54378.362')
INSERT [dbo].[wine] ([Year], [Price], [WinterRain], [AGST], [HarvestRain], [Age], [FrancePop]) VALUES (N'1978', N'7.186', N'763', N'15.8167', N'51', N'5', N'54602.193')
GO

I really recommend against syntax such as SELECT @Price =+ @Price... in T-SQL for sting aggregation. Use a more reliable solution such as STRING_AGG or FOR XML PATH. — Thom A
– Thom A ♦, Commented Oct 27, 2020 at 16:05
As for the error (Invalid object name 'dbo.wine') it's telling you the problem. I suspect you aren't connecting to the correct database. — Thom A
– Thom A ♦, Commented Oct 27, 2020 at 16:05
On a separate note, why are your Year, Age and Price columns defined as an nvarchar? They should be numerical data types, and Age should be a computed value; otherwise it'll be wrong as soon as we enter a new year after the value was inserted. — Thom A
– Thom A ♦, Commented Oct 27, 2020 at 16:07

GMB · Accepted Answer · 2020-10-27 16:12:32Z

0

Why not just use string aggregation rather than this rather unsafe =+ syntax?

declare @price nvarchar(max);
select @price = string_agg(price, ' + ') from [dbo].[wine];
select @price;

Note that you probably need more than 50 characters to store these 25 prices; I used nvarchar(max).

Demo on DB Fiddle

answered Oct 27, 2020 at 16:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return concatenated value from sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related