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
Go to Top of Page

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...
Go to Top of Page

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]
Go to Top of Page

maratasya
Starting Member

3 Posts

Posted - 2014-09-02 : 12:30:59
This seems to be working!

Thank you bitsmed!!
Go to Top of Page
   

- Advertisement -