Solved

# Pivot / Unpivot Brain Teaser

Posted on 2016-11-13
38 Views
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
``````
0
Question by:ScubeduFan
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 28

Accepted Solution

Pawan Kumar earned 250 total points (awarded by participants)
ID: 41885412

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

--
``````

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

--
``````

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
``````

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

LVL 65

Assisted Solution

Jim Horn earned 250 total points (awarded by participants)
ID: 41885456
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

LVL 28

Expert Comment

ID: 41909942
Closing via Split.

Thank you !
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month6 days, 6 hours left to enroll