Show a column values in 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 2005 Forums

Transact-SQL (2005)

Show a column values in row

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-03-31 : 00:35:27

Hi all,

I would like to show a column values in the table into row wise as below.

For example, in the below table, colulmn D should group where column C is same value and show the D value in the next row. please refer below tables.

A B C D
---------------
XS A8 SS 4
XT A7 SS 7
XY A9 YY 5
XT A7 YY 8

Result would be:

A B C
-----------
XS A8 SS
XT A7 SS
- - - 11
XY A9 YY
XT A7 YY
- - - 13

Please advise is this possible in sql server and assist.

Thanks.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-31 : 03:07:09

use with cube or with rollup , it will be easier for u

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 03:45:48

[code]DECLARE @Sample TABLE
(
A VARCHAR(9),
B VARCHAR(9),
C VARCHAR(9),
D INT
)

INSERT @Sample
SELECT 'XS', 'A8', 'SS', 4 UNION ALL
SELECT 'XT', 'A7', 'SS', 7 UNION ALL
SELECT 'XY', 'A9', 'YY', 5 UNION ALL
SELECT 'XT', 'A7', 'YY', 8

SELECT A,
B,
C,
SUM(D) AS D
FROM @Sample
GROUP BY C,
B,
A
WITH ROLLUP
HAVING GROUPING(A) ^ GROUPING(B) = 0
AND GROUPING(C) = 0[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 04:02:31

[code]SELECT A,
B,
CASE GROUPING(B)
WHEN 1 THEN NULL
ELSE C
END AS C,
SUM(D) AS D
FROM @Sample
GROUP BY C,
B,
A
WITH ROLLUP
HAVING GROUPING(A) ^ GROUPING(B) = 0
AND GROUPING(C) = 0[/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