Move columns into rows for same items

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

General SQL Server Forums

New to SQL Server Programming

Move columns into rows for same items

Author

Topic

maratasya
Starting Member

3 Posts

Posted - 2014-08-28 : 13:08:24

I have a table in the following format:

Matter ID Index Description
00103-00048 6 Litigation
00103-00048 57 Trial
00245-00015 6 Deposition
00245-00015 57 Hearing

I need each matter in a single row with descriptions as columns:

Matter ID Description_6 Description_57
00103-00048 Litigation Trial
00245-00015 Deposition Hearing

Any help appreciated!!

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-28 : 23:13:09

----------------------------------------
CREATE TABLE #MARATASYA
(
MATTER_ID VARCHAR(100),
Index_ NVARCHAR(100),
description NVARCHAR(100)
)
----------------------------------------
INSERT INTO #MARATASYA
VALUES
('00103-00048', '6', 'Litigation'),
('00103-00048', '57', 'Trial'),
('00245-00015', '6','Deposition'),
('00245-00015', '57', 'Hearing')
-----------------------------------------------
SELECT * FROM #MARATASYA

RESULTS--

MATTER_ID Index_ description
00103-00048 6 Litigation
00103-00048 57 Trial
00245-00015 6 Deposition
00245-00015 57 Hearing

-------------------------------------------
SELECT TOP 1 (MATTER_ID) AS MATTER_ID,
(SELECT description FROM #MARATASYA WHERE Index_ =6 AND MATTER_ID = '00103-00048') AS DESCRIPTION1,
(SELECT description FROM #MARATASYA WHERE Index_ =57 AND MATTER_ID = '00103-00048') AS DESCRIPTION2
FROM #MARATASYA WHERE MATTER_ID = '00103-00048'

RESULTS --

MATTER_ID DESCRIPTION1 DESCRIPTION2
00103-00048 Litigation Trial
c

maratasya
Starting Member

3 Posts

Posted - 2014-08-29 : 20:08:29

Thank you, Chris!

My table includes many matters and various descriptions... I don't think I can write an insert statement for every possible case...

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-29 : 20:56:04

How about this:

select [Matter ID]
      ,max(case when [Index]=6 then [Description] else null end) as Description_6
      ,max(case when [Index]=57 then [Description] else null end) as Description_57
  from yourtable
 group by [Matter ID]

maratasya
Starting Member

3 Posts

Posted - 2014-09-02 : 12:30:59

This seems to be working!

Thank you bitsmed!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources