SQL programmers are faced with a
requirement to generate report-like result sets directly from a Transact SQL
query. In most cases, the requirement arises from the fact that there neither
sufficient tools nor in-house expertise to develop tools that can extract the
data as a result set, and then massage the data in the desired display format.
Quite often folks are confused about the potential of breaking relational
fundamentals such as the First Normal Form or the scalar nature of typed values
Select CountryId,CountryName from Country
The objective is to return a result
set with two columns, one with the Category Identifier, and the other with a
concatenated list of all the Country Names separated by a delimiting character:
such as a comma.
Concatenating column values or
expressions from multiple rows are usually best done in a client side
application language, since the string manipulation capabilities of Transact
SQL and SQL based DBMSs are somewhat limited. However, you can do these using
different approaches in Transact SQL, but it is best to avoid such methods in
long-term solutions
Here I have Some Query to Concatenate
Columns values in Rows.
select
STUFF((Select ','+CountryName
from Country T1
FOR XML PATH('')),1,1,'') as CountryName
---------------------------------------------------------------
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + CountryName
FROM Country
---------------------------------------------------------------
--DECLARE @Names
VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(CountryName, 'N/A')
FROM Country
print @Names
---------------------------------------------------------------
--DECLARE @Names
VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + CountryName
FROM Country
WHERE
CountryName IS NOT
NULL
print @Names
---------------------------------------------------------------
Select Main.CountryId,
Left(Main.Country,Len(Main.Country)-1) As
"CountryName"
From
(
Select distinct ST2.CountryId,
(
Select
ST1.CountryName +
',' AS [text()]
From
Country ST1
Where
ST1.CountryId =
ST2.CountryId
ORDER
BY ST1.CountryId
For
XML PATH ('')
)
[Country]
From
Country ST2
) [Main]
---------------------------------------------------------------
Select distinct ST2.CountryId,
substring(
(
Select
','+ST1.CountryName AS [text()]
From
Country ST1
Where
ST1.CountryId =
ST2.CountryId
ORDER
BY ST1.CountryId
For
XML PATH ('')
), 2, 1000) [Country]
From Country
ST2
---------------------------------------------------------------
SELECT
CountryName + ', '
AS 'data()'
FROM Country
FOR XML PATH('')
---------------------------------------------------------------
SELECT Stuff(
(SELECT N', ' + CountryName FROM Country FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'') as CountryName
---------------------------------------------------------------
declare @rowsCount
INT
declare @i INT = 1
declare @names varchar(max) = ''
DECLARE @MyTable TABLE
(
Id int identity,
Name varchar(500)
)
insert into @MyTable select
CountryName from Country
set @rowsCount
= (select COUNT(Id) from @MyTable)
while @i < @rowsCount
begin
set @names = @names + ', ' + (select name from
@MyTable where Id =
@i)
set @i = @i + 1
end
select @names
---------------------------------------------------------------
SELECT CountryId,
MAX( CASE seq WHEN 1 THEN
CountryName ELSE ''
END ) + ', ' +
MAX( CASE seq WHEN 2 THEN
CountryName ELSE ''
END ) + ', ' +
MAX( CASE seq WHEN 3 THEN
CountryName ELSE ''
END ) + ', ' +
MAX( CASE seq WHEN 4 THEN
CountryName ELSE ''
END )
FROM ( SELECT p1.CountryId, p1.CountryName,
( SELECT COUNT(*)
FROM Country p2
WHERE p2.CountryId = p1.CountryId
AND p2.CountryName <= p1.CountryName )
FROM
Country p1 ) D ( CountryId,
CountryName, seq )
GROUP BY CountryId ;
---------------------------------------------------------------
SELECT CountryId,
"1" + ', ' + "2" + ', ' + "3" + ', ' + "4" AS
CountryName
FROM ( SELECT CountryId, CountryName,
ROW_NUMBER() OVER (PARTITION BY CountryId
ORDER
BY CountryName)
FROM
Country ) P ( CountryId,
CountryName, seq )
PIVOT ( MAX( CountryName ) FOR seq IN ( "1",
"2", "3", "4" ) ) AS P_ ;
---------------------------------------------------------------
WITH CTE ( CountryId, product_list,CountryName, length )
AS ( SELECT CountryId, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM
Country
GROUP
BY CountryId
UNION
ALL
SELECT
p.CountryId, CAST( product_list
+
CASE
WHEN length = 0 THEN '' ELSE ', ' END + CountryName AS VARCHAR(8000) ),
CAST( CountryName AS VARCHAR(8000)), length + 1
FROM
CTE c
INNER
JOIN Country p
ON
c.CountryId = p.CountryId
WHERE
p.CountryName >
c.CountryName )
SELECT CountryId, product_list
FROM ( SELECT CountryId, product_list,
RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )
FROM
CTE ) D ( CountryId,
product_list, rank
)
WHERE rank = 1 ;
---------------------------------------------------------------
SELECT top 1 p1.CountryId,
( SELECT CountryName + '-'
FROM
Country p2
--WHERE
p2.CountryName = p1.CountryName
ORDER
BY CountryName
FOR
XML PATH('') ) AS Country
FROM
Country p1
-- GROUP BY
CountryId ;
Thanks for comments.....