how to show row wise data columnwise data

Question

how to show row wise data columnwise data

imran-399399

SSC Enthusiast

Points: 179
More actions
February 16, 2008 at 4:44 am

#203547

Hi All,
i am using sql server 2005.
in a table a record is entered as product1 , status1 ,product2 , status2 , product3 , status3 in a row now i have to display it as
product1 status1
product2 status2
product3 status3
how can i do it .
please help.

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Since you didn't say what the datatype of the CSV column is, and since you said 2005", I can only assume the "worst". This would run much faster with a Tally table instead of a "smart" CTE that acts like one, but this works.

The following code is fully operational test code. You'll need to make changes for the actual table and column names. Lemme know how it works out for ya...

--===== Create a sample table with data...

-- THIS IS NOT PART OF THE SOLUTION

CREATE TABLE #yourtable

(

PK INT IDENTITY(1,1),

CSVData VARCHAR(MAX)

)

INSERT INTO #yourtable

(CSVData)

SELECT 'product1 , status1 ,product2 , status2 , product3 , status3' UNION ALL

SELECT 'Prod10,Status10,Prod11,Status11,Prod12,Status12,Prod13,Status13,Prod14,Status14' UNION ALL

SELECT 'Prod15,Status15'

;WITH cteTally AS

(--==== Create a "Tally Table" with a count of 1 to N where N is the max len

-- of the CSVData column +2

SELECT TOP (SELECT MAX(LEN(CSVData))+2 FROM #yourtable)

ROW_NUMBER() OVER (ORDER BY t1.OBJECT_ID) AS N

FROM Master.sys.ALL_COLUMNS t1

CROSS JOIN

Master.sys.ALL_COLUMNS t2

)

,

cteSplit AS

(--==== Do the split and calculate the row and column numbers so we can reassemble later

SELECT PK,

Element = (RANK() OVER (PARTITION BY y.PK ORDER BY t.n)), --Just for checking

Row = (RANK() OVER (PARTITION BY y.PK ORDER BY t.n)-1)/2+1, --Sneaky key to whole thing

Col = (RANK() OVER (PARTITION BY y.PK ORDER BY t.n)-1)%2+1, --Sneaky key to whole thing

Data = RTRIM(LTRIM(NULLIF(

SUBSTRING(','+y.CSVData,

t.N+1,

CHARINDEX(',', y.CSVData+',', t.N+1)-t.N-1)

,'')))

FROM cteTally t

CROSS JOIN #yourtable y

WHERE t.N < LEN(y.CSVData)

AND SUBSTRING(','+y.CSVData, t.N, 1) = ','

)

--===== Reassemble the split data using the precacalculated row and column numbers

-- using a simple "Crosstab" (as fast or faster than PIVOT in most cases)

SELECT MIN(CASE WHEN Col = 1 THEN Data END) AS Product,

MIN(CASE WHEN Col = 2 THEN Data END) AS [Status]

FROM cteSplit

GROUP BY PK,Row

ORDER BY PK,Row

DROP TABLE #yourtable

By the way, in case anyone is interested... this also works for PASSING AN "ARRAY" OF INFORMATION as a parameter. This one just happens to be a 2 column "array"... changing a couple of things like /2 and %2 in a couple of spots and changing the CorssTab code (add extra columns to that), will allow you to setup for virtually any number of columns in the "array" parameter.

--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)
Intro to Tally Tables and Functions

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

Just curious... did that solve your problem or did you die? 😉

--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)
Intro to Tally Tables and Functions

ashoknegi84-604617 Grasshopper Points: 13 More actions · Answer 3

hi

i have a table like

empname empid empsal

---------------------------

abc 1 300000

def 2 500000

fgh 3 700000

and op should be

abc def fgh

1 2 3

300000 500000 700000

Thanks:)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

ashoknegi84 (4/1/2008)
hi
i have a table like
empname empid empsal
---------------------------
abc 1 300000
def 2 500000
fgh 3 700000
and op should be
abc def fgh
1 2 3
300000 500000 700000
Thanks:)

If you have a table with only 3 rows in it, then do it manually. 😉

If the table has more than 3 rows in it, then you need to post more realistic data so we can show you how... please refer to the URL in my signature line before you even think of posting more data...

Also, when you do post some data, you'll need to identify which column you want to group on... you don't have a "grouping" column as an EAV might to identify all the parts of an individual row.

--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)
Intro to Tally Tables and Functions

sajjan_jha2 Valued Member Points: 69 More actions · Answer 5

ashoknegi84 (4/1/2008)
hi
i have a table like
empname empid empsal
---------------------------
abc 1 300000
def 2 500000
fgh 3 700000
and op should be
abc def fgh
1 2 3
300000 500000 700000
Thanks:)

inform.nitesh Grasshopper Points: 15 More actions · Answer 6

Use this command 🙂

select rtrim (xmlagg (xmlelement (e, empno|| ',')).extract ('//text()'), ',') enames

from emp

By

Nitesh Jyotirmay