[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

Pivot / Unpivot Brain Teaser

I'm looking to group records based on their values.
Below is an example of my data nd the output I am looking to achieve.
Any assistance would be greatly appreciated.

ID  LineID  F1     F2        F3
1   1       Joe    Thomas    Smith
1   1       David  Harry     Jones
1   2       Joe    Thomas    Smith
1   2       David  Hary      Jones
1   3       Joe    Thomas    Smith
1   4       Joe    Thomas    Smith
1   4       David  Harry     Jones
1   4       Carlos Jay       Smith
2   1       Jane   Janet     Garcia


Results I am trying to get.

ID  LineID  F1     F2        F3      R
1   1       Joe    Thomas    Smith   Joe~Thomas~Smith|David~Harry~Jones
1   1       David  Harry     Jones   Joe~Thomas~Smith|David~Harry~Jones
1   2       Joe    Thomas    Smith   Joe~Thomas~Smith|David~Harry~Jones
1   2       David  Hary      Jones   Joe~Thomas~Smith|David~Harry~Jones
1   3       Joe    Thomas    Smith   Joe~Thomas~Smith
1   4       Joe    Thomas    Smith   Joe~Thomas~Smith|David~Harry~Jones
1   4       David  Harry     Jones   Joe~Thomas~Smith|David~Harry~Jones
1   4       Carlos Jay       Smith   Joe~Thomas~Smith|David~Harry~Jones|Carlos~Jay~Smith
2   1       Jane   Janet     Garcia  Jane~Janet_Garcia

Open in new window

0
ScubeduFan
Asked:
ScubeduFan
  • 2
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
Here it is, Please try

Table Creation

--

CREATE TABLE testPivot
(
	 ID     TINYINT 
	,LineID TINYINT 
	,F1     VARCHAR(25)
	,F2     VARCHAR(25)     
	,F3     VARCHAR(25)
)
GO

INSERT INTO testPivot VALUES
(1,   1,       'Joe'   , 'Thomas',    'Smith'),
(1,   1,       'David' , 'Harry'  ,   'Jones'),
(1,   2,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   2,       'David' , 'Hary'    ,  'Jones'),
(1,   3,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   4,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   4,       'David' , 'Harry'   ,  'Jones'),
(1,   4,       'Carlos', 'Jay'     ,  'Smith'),
(2,   1,       'Jane',   'Janet'   ,  'Garcia')
GO

--

Open in new window


Query - 1

--

SELECT m.ID, m.LineID , m.F1 , m.F2 , m.F3 ,  STUFF 
                ((
					SELECT '| ' + m2.N
					FROM (SELECT *, CONCAT(F1,'~',F2,'~',F3) N FROM testPivot) m2
					WHERE ( m.LineID = m2.LineID AND m.ID = m2.ID )
					FOR XML PATH('')
					) ,1,2,'') 
                R
FROM
(
	SELECT *, CONCAT(F1,'~',F2,'~',F3) N FROM testPivot
)m

--

Open in new window


Query 2

;WITH CTE AS
(
	SELECT *, F1 + '~' + F2 + '~' + F3 N FROM testPivot
)
SELECT m.ID, m.LineID , m.F1 , m.F2 , m.F3 ,  STUFF 
                ((
					SELECT '| ' + m2.N
					FROM CTE m2
					WHERE ( m.LineID = m2.LineID AND m.ID = m2.ID )
					FOR XML PATH('')
					) ,1,2,'') 
                R
FROM CTE m

Open in new window


Output

ID      LineID        F1      F2      F3                 R
1      1      Joe      Thomas      Smith      Joe~Thomas~Smith| David~Harry~Jones
1      1      David      Harry      Jones      Joe~Thomas~Smith| David~Harry~Jones
1      2      Joe      Thomas      Smith      Joe~Thomas~Smith| David~Hary~Jones
1      2      David      Hary      Jones      Joe~Thomas~Smith| David~Hary~Jones
1      3      Joe      Thomas      Smith      Joe~Thomas~Smith
1      4      Joe      Thomas      Smith      Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
1      4      David      Harry      Jones      Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
1      4      Carlos      Jay      Smith      Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
2      1      Jane      Janet      Garcia      Jane~Janet~Garcia

Hope it helps !!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For an image and code-heavy demo of what you're asking check out T-SQL:  Normalized data to a single comma delineated string and back
0
 
Pawan KumarDatabase ExpertCommented:
Closing via Split.

Given what was asked.

Thank you !
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now