CODEWITHSUNDEEP
sql-server
Valeria
Valeria

Reputation: 1362

SQL Server : concatenate results of 2 queries as a string

I am new to SQL, and I need to make a string out of the UNION result. I have seen many similar questions but they were either related to concatenating results of a single SQL query, or they were using JOIN on some kind of row id, while I do not need this and do not have any column on which I can use JOIN.

I have the following UNION:

(
    SELECT COUNT(*)
    FROM [db].[table1]
    WHERE [ItemType] = 2
)
UNION
(
    SELECT TOP(1) Items
    FROM [db].[table2]
    WHERE [ItemType] = 2
)

It returns a simple result with two rows:

15
10

15 is the total number of items, and 10 is the number of items left available.

I want to return a table with only one entry 10/15. What is the simplest way to achieve it? Thanks.

Upvotes: 0

Views: 2118

Answers (3)

Cody McPheron
Cody McPheron

Reputation: 135

It's a fairly quick operation so for readability I'd try something like this. You can format your results easily at the bottom too, should you need to add dashes or extra spaces too.

DECLARE @A int, @B int

SET @A = ( SELECT COUNT(*) FROM [db].[table1] WHERE [ItemType] = 2 )

SET @B = ( SELECT TOP(1) Items FROM [db].[table2] WHERE [ItemType] = 2 )

SELECT (@A + '/' + @B) as result

Upvotes: 0

Conor Cunningham MSFT
Conor Cunningham MSFT

Reputation: 4481

You have lurking problems in what you are trying to do.

First, UNION is a distincting operation. if you happen to have 15 total items AND 15 available, you'll only get one row back. That's not what you want. (UNION ALL would fix this, but you don't need UNION stuff at all).

Your next issue(?) may be your data model choice. The second table (Items) has values in it - you are pulling one row out of a particular type, but you don't have any control over which one gets pulled out. It could be any value in the set. If you want the count of items available, as opposed to the first item randomly that SQL picks for you, then you may want to adjust your query. (For "give me the first item and it actually is the count", you would add an ORDER BY into that subselect to help SQL pick the proper "first" order in a given sort. For "I want the count of distinct items in this table, you might want count(*) or count(distinct item) depending on your semantic).

Once you sort these two things out, you can then use two subselects to get each scalar value and then convert them to strings as you are attempting to do in your example. Here's an example on how the pattern to do this should look once you clarify your data model issue

select convert(nvarchar(100),a) + '/' + convert(nvarchar(100),b) FROM
(
select (select count(*) from sys.objects) as a, (select count(*) from sys.objects) as b
) C

Result: 101/101

Upvotes: 1

Thom A
Thom A

Reputation: 95544

A bit of a guess, but perhaps:

SELECT CONCAT((SELECT COUNT(*) FROM [db].[table1] WHERE [ItemType] = 2)),'/',
              (SELECT TOP(1) Items FROM [db].[table2] WHERE [ItemType] = 2 ORDER BY {Your Column}));

Note the {Your Column} which you need to replace with an appropriate column to give the correct consistent result. Having a TOP without an ORDER BY can (and will) produce inconsistent results, as tables in SQL Server are stored in unordered heaps; therefore the "TOP 1" will be whatever row SQL Server "finds" (retrieves) first from your table and is effectively random.

Upvotes: 4

Related Questions