Display the record in column wise

Question

Display the record in column wise

rameshduraikannu

SSC Veteran

Points: 211
More actions
December 25, 2009 at 2:39 am

#213931

Hai friends
I want to display the data in column wise
the actual is look like this
location
-------
loc1
loc2
loc3
loc4
loc5
loc6
i want to display the like this way
location location location
------- ------- --------
loc1 loc2 loc3
loc4 loc5 loc6
i want to display 3 column and 3 row.
it is possible to display the data like this way

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sathish Kumar.T Grasshopper Points: 15 More actions · Answer 1

Hi,

I think you can use Pivot Concept to display Rows into Columns, but not sure about splitting the results into 2 rows as you expect.

Regards,

Sathish

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

rameshduraikannu,

Since you're a newbie, take a look at the first link in my signature line below... you will get MUCH better answers MUCH more quickly if you post both the table creation and data in such a readily consumable format.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Ok... here's a demonstrable solution based on the data given. As a side bar, please see the following URL for how to do such a thing and why the method used is frequently better than using PIVOT.

http://www.sqlservercentral.com/articles/T-SQL/63681/

To satisfy any intellectual curiosity about the simple integer math that makes this all possible, run the following code, then run just the SELECT that makes up the CTE and you will see. A trip to Books Online to see what the % operator does would be worthwhile, as well.

--===== Create and populate a test table.

-- Note that this is NOT a part of the solution.

-- It's just to demo the upcoming code with.

IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

DROP TABLE TestTable;

SELECT Location

INTO #TestTable

FROM (

SELECT 'loc1' UNION ALL

SELECT 'loc2' UNION ALL

SELECT 'loc3' UNION ALL

SELECT 'loc4' UNION ALL

SELECT 'loc5' UNION ALL

SELECT 'loc6'

)tt (Location);

--===== Solve the problem

WITH

ctePreNumber AS

(

SELECT (ROW_NUMBER() OVER (ORDER BY Location)-1)/3 AS RowNum,

(ROW_NUMBER() OVER (ORDER BY Location)-1)%3 AS ColNum,

Location

FROM #TestTable

)

SELECT MAX(CASE WHEN ColNum = 0 THEN Location END) AS Location1,

MAX(CASE WHEN ColNum = 1 THEN Location END) AS Location2,

MAX(CASE WHEN ColNum = 2 THEN Location END) AS Location3

FROM ctePreNumber

GROUP BY RowNum

ORDER BY RowNum

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rameshduraikannu SSC Veteran Points: 211 More actions · Answer 4

hai Jeff Moden,

Thank for solution and two link given below from u post.

i realize what mistake done by me.

And once again thank for your valueable post and i learned

Cross Tabs and Pivots from ur link.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

rameshduraikannu,

That's what we like to hear. Thanks for the feedback and glad to have been able to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)