Sql Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql Query

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-07-09 : 10:30:39

Hello All

I am struck in a query and want some help. I have a table name table1 which contain two coulumn columnA and columnB as below

columnA------columnB
12------A
12------B
13------A
14------C
15------B
13------B
16------A
15------C
16------B

I want to write the sql query which give output as below

Output Required

ColumnA------Count------Common
12------2------A,B
13------2------A,B
14------1------C
15------2------B,C
16------2------A,B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-09 : 10:49:40

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ColumnA TINYINT NOT NULL,
ColumnB CHAR(1) NOT NULL
);

INSERT @Sample
(
ColumnA,
ColumnB
)
VALUES (12, 'A'),
(12, 'B'),
(13, 'A'),
(14, 'C'),
(15, 'B'),
(13, 'B'),
(16, 'A'),
(15, 'C'),
(16, 'B');

-- SwePeso
SELECT b.ColumnA,
b.Items AS [Count],
STUFF(f.Data, 1, 1, '') AS Common
FROM (
SELECT ColumnA,
COUNT(*) AS Items
FROM @Sample
GROUP BY ColumnA
) AS b
CROSS APPLY (
SELECT DISTINCT ',' + x.ColumnB
FROM @Sample AS x
WHERE x.ColumnA = b.ColumnA
ORDER BY ',' + x.ColumnB
FOR XML PATH('')
) AS f(Data)
ORDER BY b.ColumnA[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources