need help - 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)

need help

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-07-26 : 15:01:47

Hello Guys,
I have a table with below sample data. I need to pull the data group by Field1, add Field2 into field3 seperating by ; if they are different value and Field4 need to count how many record it has.

declare @tbl1 table
(ID INT,
Field1 varchar(10),
Field2 varchar(50),
Field3 varchar(255),
Field4 int
)

INSERT INTO @tbl1
SELECT 1,'Aa' ,'X',null,null

INSERT INTO @tbl1
SELECT 2,'Aa' ,'X',null,null

INSERT INTO @tbl1
SELECT 3,'Aa' ,'XX',null,null

INSERT INTO @tbl1
SELECT 4,'Aa' ,'XXX',null,null

INSERT INTO @tbl1
SELECT 5,'Bb' ,'Y',null,null

INSERT INTO @tbl1
SELECT 6,'Cc' ,'Z',null,null

INSERT INTO @tbl1
SELECT 7,'Cc' ,'ZZ',null,null

select * from @tbl1
go

select Field1,Field3,Field4 from @tbl1
The Output looks like :
Aa X;XX;XXX 4
Bb Y 1
Cc Z;ZZ 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-26 : 15:43:38

[code]SELECT a.Field1,
STUFF(f.Data, 1, 1, '') AS Data,
g.Cnt
FROM (
SELECT Field1
FROM @tbl1
GROUP BY Field1
) AS a
CROSS APPLY (
SELECT ';' + Field2
FROM @tbl1 AS t1
WHERE t1.Field1 = a.Field1
GROUP BY Field2
FOR XML PATH('')
) AS f(Data)
CROSS APPLY (
SELECT COUNT(*)
FROM @tbl1 AS t1
WHERE t1.Field1 = a.Field1
) AS g(Cnt)[/code]

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

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 05:18:33

quote:

Originally posted by Peso

SELECT		a.Field1,
		STUFF(f.Data, 1, 1, '') AS Data,
		g.Cnt
FROM		(
			SELECT		Field1
			FROM		@tbl1
			GROUP BY	Field1
		) AS a
CROSS APPLY	(
			SELECT		';' + Field2
			FROM		@tbl1 AS t1
			WHERE		t1.Field1 = a.Field1
			GROUP BY	Field2
			FOR XML		PATH('')
		) AS f(Data)
CROSS APPLY	(
			SELECT	COUNT(*)
			FROM	@tbl1 AS t1
			WHERE	t1.Field1 = a.Field1
		) AS g(Cnt)

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

I think there is no need for using additional Apply clause to get the count.It can be acheieved in the same Apply clause used for "a"


SELECT		a.Field1,
		STUFF(f.Data, 1, 1, '')AS Data,
                a.cnt
FROM		(
			SELECT		Field1,COUNT(Field1)as cnt
			FROM		@tbl1
			GROUP BY	Field1
		) AS a
CROSS APPLY	(
			SELECT		';' + Field2
			FROM		@tbl1 AS t1
			WHERE		t1.Field1 = a.Field1
			GROUP BY	Field2
			FOR XML		PATH('')
		) AS f(Data)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources