Help in SQL Reqd. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help in SQL Reqd.

Author

Topic

koushikchandra
Starting Member

24 Posts

Posted - 2010-10-25 : 02:40:27

Hi,

I have a table like below :

create table my_tab_2
(
col1 char(10),
col2 char(10)
)

And I have the following data in that table :

insert into my_tab_2 values ('Grp1','A')
insert into my_tab_2 values ('Grp1','B')
insert into my_tab_2 values ('Grp1','C')
insert into my_tab_2 values ('Grp2','D')
insert into my_tab_2 values ('Grp3','E')
insert into my_tab_2 values ('Grp3','F')

Now, I want the output as :
Col1 Grp2
-------------
Grp1 A,B,C
Grp2 D
Grp3 E,F

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 03:07:50

Have a look at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

koushikchandra
Starting Member

24 Posts

Posted - 2010-10-25 : 03:51:36

I am looking to write it in a single sql. Can I achieve it?

koushikchandra
Starting Member

24 Posts

Posted - 2010-10-25 : 04:14:24

I am able to write it using the following sql :

WITH MyCTE (COL1, COL2, RowVersion)
AS(
SELECT
COL1
,COL2
,ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2 ASC) RowVersion
FROM MY_TAB_2
)
SELECT BASE_COL1,
CASE WHEN COALESCE(LAG_Col2,'Z')='Z' THEN LTRIM(RTRIM(BASE_COL2))
WHEN COALESCE(LEAD_COL2,'Z')='Z' THEN COALESCE(LTRIM(RTRIM(LAG_Col2)),'')+','+LTRIM(RTRIM(BASE_COL2))
ELSE COALESCE(LTRIM(RTRIM(LAG_Col2)),'')+','+LTRIM(RTRIM(BASE_COL2))+','+COALESCE(LTRIM(RTRIM(LEAD_COL2)),'')
END
FROM
(
SELECT BASE_COL1,
LEAD_COL1,
LAG.COL1 LAG_Col1,
BASE_COL2,
LEAD_COL2,
LAG.COL2 LAG_COL2,
ROW_NUMBER() over (partition by base_col1 order by COALESCE(lag.col2,'Z'),base_col2,COALESCE(lead_col2,'Z') asc) RN
FROM
(
SELECT BASE.COL1 BASE_COL1
,LEAD.COL1 LEAD_COL1
,BASE.COL2 BASE_COL2
,LEAD.COL2 LEAD_COL2
,BASE.RowVersion BASE_RowVersion
,LEAD.RowVersion LEAD_RowVersion
FROM MyCTE BASE
LEFT JOIN MyCTE LEAD ON BASE.COL1 = LEAD.COL1
AND BASE.RowVersion = LEAD.RowVersion-1
) T1
LEFT JOIN MyCTE LAG ON BASE_COL1 = LAG.COL1
AND BASE_RowVersion = LAG.RowVersion+1
) T2
WHERE RN = 1

Can any one let me know that is there any easier way of doing it

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 04:26:08

quote:
Originally posted by koushikchandra

I am looking to write it in a single sql. Can I achieve it?

Did u check the link ?

In the link in the first post you can find three select statement and any one will give you required output.

Extract from the link:

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

Why you want to go for CTE ?

koushikchandra
Starting Member

24 Posts

Posted - 2010-10-25 : 05:08:40

Thanks for your input.

I missed the SQLs in the first look.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 05:30:42

quote:
Originally posted by koushikchandra

Thanks for your input.

I missed the SQLs in the first look.

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources