Display row entries in 3 columns

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 2008 Forums

Transact-SQL (2008)

Display row entries in 3 columns

Author

Topic

madlo
Starting Member

41 Posts

Posted - 2011-09-28 : 10:34:18

Hi,

I need to output row data in 3 columns per row instead of 1 and want to do it by using the database sql and not the frontend.

Data must get display 3 row entries in one row to save space visually.
Row data displayed horizontally first then vertically the next row.
Example:

Table Assessment
SubjectCode(int)
PercentageSemA (int)
PercentageSemB (int)

Data:
1,56,65
2,63,42
3,87,62
4,41,98
5,76,65

Desired Output:
Subject_1--SemA_1--SemB_1--Subject_2--SemA_2--SemB_2--Subject_3--SemA_3--SemB_3
1,56,65,2,63,42,3,87,62
4,41,98,5,76,65

I imagine doing a TSQL ROW_NUMBER() first and then using the ROW_NUMBER() in combination with the Math function MOD(%) dividing by 3 and then from there pivot it.

What is the best TSQL method to output what I want?

gvmk27
Starting Member

44 Posts

Posted - 2011-09-28 : 10:53:14

This should work...

DECLARE @result varchar(255)
SET @result = ''

SELECT @result = @result + cast(SubjectCode as nvarchar) + ',' + cast(PercentageSemA as nvarchar) + ',' + cast(PercentageSemB as nvarchar) + ','
FROM Assessment
print @result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:00:44

yep. that method should do the trick


SELECT MAX(CASE WHEN (rn-1) % 3 =1 THEN SubjectCode) AS Subject_1,
SELECT MAX(CASE WHEN (rn-1) % 3 =1 THEN PercentageSemA ) AS SemaA_1,
SELECT MAX(CASE WHEN (rn-1) % 3 =1 THEN PercentageSemB ) AS SemaB_1,
SELECT MAX(CASE WHEN (rn-1) % 3 =2 THEN SubjectCode) AS Subject_2,
SELECT MAX(CASE WHEN (rn-1) % 3 =2 THEN PercentageSemA ) AS SemaA_2,
SELECT MAX(CASE WHEN (rn-1) % 3 =2 THEN PercentageSemB ) AS SemaB_2,
SELECT MAX(CASE WHEN (rn-1) % 3 =3 THEN SubjectCode) AS Subject_3,
SELECT MAX(CASE WHEN (rn-1) % 3 =3 THEN PercentageSemA ) AS SemaA_3,
SELECT MAX(CASE WHEN (rn-1) % 3 =3 THEN PercentageSemB ) AS SemaB_3
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY SubjectCode) AS rn,*
FROM Table
)t
GROUP BY (rn-1)/3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madlo
Starting Member

41 Posts

Posted - 2011-09-28 : 11:01:17

quote:
Originally posted by gvmk27

This should work...

DECLARE @result varchar(255)
SET @result = ''

SELECT @result = @result + cast(SubjectCode as nvarchar) + ',' + cast(PercentageSemA as nvarchar) + ',' + cast(PercentageSemB as nvarchar) + ','
FROM Assessment
print @result

Sorry I need to display rows as columns but only 3 rows at a time not all the rows.

madlo
Starting Member

41 Posts

Posted - 2011-09-28 : 11:22:56

quote:

Originally posted by visakh16

yep. that method should do the trick


SELECT MAX(CASE WHEN (rn-1) % 3 =1 THEN SubjectCode) AS Subject_1,
SELECT MAX(CASE WHEN (rn-1) % 3 =1 THEN PercentageSemA ) AS SemaA_1,
SELECT MAX(CASE WHEN (rn-1) % 3 =1 THEN PercentageSemB ) AS SemaB_1,
SELECT MAX(CASE WHEN (rn-1) % 3 =2 THEN SubjectCode) AS Subject_2,
SELECT MAX(CASE WHEN (rn-1) % 3 =2 THEN PercentageSemA ) AS SemaA_2,
SELECT MAX(CASE WHEN (rn-1) % 3 =2 THEN PercentageSemB ) AS SemaB_2,
SELECT MAX(CASE WHEN (rn-1) % 3 =3 THEN SubjectCode) AS Subject_3,
SELECT MAX(CASE WHEN (rn-1) % 3 =3 THEN PercentageSemA ) AS SemaA_3,
SELECT MAX(CASE WHEN (rn-1) % 3 =3 THEN PercentageSemB ) AS SemaB_3
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY SubjectCode) AS rn,*
FROM Table
)t
GROUP BY (rn-1)/3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks I tested and it works and you did it without using pivot. Not sure I understand the group by off a formula method.
Is the performance better than pivot?

[Code]

SELECT MAX(CASE WHEN (rn-1) % 3 =0 THEN SubjectCode END) AS Subject_1,
MAX(CASE WHEN (rn-1) % 3 =0 THEN PercentageSemA END) AS SemaA_1,
MAX(CASE WHEN (rn-1) % 3 =0 THEN PercentageSemB END) AS SemaB_1,
MAX(CASE WHEN (rn-1) % 3 =1 THEN SubjectCode END) AS Subject_2,
MAX(CASE WHEN (rn-1) % 3 =1 THEN PercentageSemA END) AS SemaA_2,
MAX(CASE WHEN (rn-1) % 3 =1 THEN PercentageSemB END) AS SemaB_2,
MAX(CASE WHEN (rn-1) % 3 =2 THEN SubjectCode END) AS Subject_3,
MAX(CASE WHEN (rn-1) % 3 =2 THEN PercentageSemA END) AS SemaA_3,
MAX(CASE WHEN (rn-1) % 3 =2 THEN PercentageSemB END) AS SemaB_3
FROM
(

SELECT ROW_NUMBER() OVER (ORDER BY SubjectCode) AS rn,*
FROM
(
SELECT 1 AS SubjectCode,50 AS PercentageSemA,60 AS PercentageSemB UNION
SELECT 2 AS SubjectCode,51 AS PercentageSemA,61 AS PercentageSemB UNION
SELECT 3 AS SubjectCode,52 AS PercentageSemA,62 AS PercentageSemB UNION
SELECT 4 AS SubjectCode,53 AS PercentageSemA,63 AS PercentageSemB UNION
SELECT 5 AS SubjectCode,54 AS PercentageSemA,64 AS PercentageSemB UNION
SELECT 6 AS SubjectCode,55 AS PercentageSemA,65 AS PercentageSemB UNION
SELECT 7 AS SubjectCode,56 AS PercentageSemA,66 AS PercentageSemB
)T0

)T1
GROUP BY (rn-1)/3
[/Code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:55:12

its just another way of pivoting. You can use PIVOT method also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources