Display a record in 1 row - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Display a record in 1 row

Author

Topic

hanifikri
Starting Member

14 Posts

Posted - 2009-03-11 : 05:36:11

I have a table which stored the record as below:

EmpID OTCode OTAmt
Emp01 OT1 100
Emp01 OT2 150
Emp02 OT2 200
Emp03 OT1 500

I would like to display the record like this:

EmpID OT1 OT2
Emp01 100 150
Emp02 0 200
Emp03 500 0

Does anyone of you know how to do it?

Please advise.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 05:45:54

SELECT EmpID,
SUM(CASE WHEN OTCode = 'OT1' THEN OTAmt ELSE 0 END) AS OT1,
SUM(CASE WHEN OTCode = 'OT2' THEN OTAmt ELSE 0 END) AS OT2
FROM Table1
GROUP BY EmpID

E 12°55'05.63"
N 56°04'39.26"

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-03-12 : 05:16:19

hi peso,
how to convert
the following table

col1 col2 col3 col4
aaa a10 a11 fft1
aaa a20 a12 fft1

to

col1 col2 col3
aaa a10 a11
aaa a20 a12
aaa col4 fft1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 14:10:49

how did you get col4 as value of col2 in last row? can you explain that?

hanifikri
Starting Member

14 Posts

Posted - 2009-03-12 : 21:32:11

thanks peso.

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-03-13 : 00:55:04

hi visakh,

I need the output as table2
can u help me to write a query that gives an output as table2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 03:13:11

[code]DECLARE @Sample TABLE
(
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10)
)

INSERT @Sample
SELECT 'aaa', 'a10', 'a11', 'fft1' UNION ALL
SELECT 'aaa', 'a20', 'a12', 'fft1'

SELECT Col1,
Col2,
Col3
FROM @Sample

UNION ALL

SELECT DISTINCT
Col1,
'Col4',
Col4
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources