Home > TSQL > TSQL – List of GroupName and GroupMembers

TSQL – List of GroupName and GroupMembers

Recently, one of my friend contacted me to solve an issue to get Group Name and Group Members as a list from a table. They want a query to use as source query to get the data to a report.

I haven’t asked any questions regarding what the data volume and performance related issues.

In order to solve this problem, I simulated the table structure and sample data as below

Step 1:

Creating a sample Database BITEST

CREATE DATABASE [BITEST]

Step 2:

Creating table


CREATE TABLE GroupTest

(
GroupMember VARCHAR(50) NULL

,GroupName VARCHAR(50) NULL

)

Step 3:

Create sample record set into table [GroupTest]


INSERT INTO GroupTest (

GroupMember,

GroupName

)

VALUES
('Group Member A1','Group A'),
('Group Member A2','Group A'),
('Group Member B1','Group B'),
('Group Member B2','Group B'),
('Group Member C1','Group C'),
('Group Member C2','Group C');

Step 4:

Varify the data in the table

USE [BITEST]
SELECT *
FROM [dbo].[GroupTest]
Source table data

Source table data

USE [BITEST]

DECLARE @GroupName TABLE (GroupName VARCHAR(50));
DECLARE @GroupList TABLE (GroupList VARCHAR(50));
DECLARE @cNext VARCHAR(50);
	INSERT INTO @GroupName
	SELECT GroupName
	FROM [dbo].[GroupTest]
	GROUP BY GroupName
DECLARE C CURSOR FOR
	SELECT GroupName
	FROM @GroupName;
OPEN C ;
	FETCH NEXT FROM C INTO @cNext ;
		WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @GroupList
		SELECT @cNext
		INSERT INTO @GroupList
		SELECT GroupMember FROM [dbo].[GroupTest] GT
		WHERE GT.GroupName = @cNext;
		FETCH NEXT FROM c INTO @cNext
	END
CLOSE C ;
DEALLOCATE C ;
SELECT * FROM @GroupList

Result:

Result List

Result List

Points to consider:

  1.  I have used select * instead of individual columns in my query, I know not a best practice but later I informed to change the query as per their performance results.
  2. There are many ways to solve this issue but I choose to solve as quickly as possible.
Advertisements
Categories: TSQL Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: