I want to display the rowdata with columnnames is there any posibility

Question

Post reply

I want to display the rowdata with columnnames is there any posibility

dastagirid

SSCommitted

Points: 1810
More actions
March 18, 2009 at 11:32 pm

#129902

hi,
I have attached the jpg file.In this the table have
list column value
dastagiri
9703009129
kadapa
male
venkat
970321564
hyderabad
male like this ..
I want to display
column1 column2 column3 column4
dastagiri 9703009129 kadapa male
venkat 970321564 hyderabad male
Thanks
Dastagiri.D

Viewing 9 posts - 1 through 8 (of 8 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this

declare @abc table (listrowid int, listcolid int,listcolumnid varchar(15))

insert into @abc values (1,1,'1name1')

insert into @abc values (1,2,'1name2')

insert into @abc values (1,3,'1name3')

insert into @abc values (1,6,'1name4')

insert into @abc values (2,1,'2name1')

insert into @abc values (2,2,'2name2')

insert into @abc values (2,3,'2name3')

insert into @abc values (2,6,'2name4')

insert into @abc values (3,1,'3name1')

insert into @abc values (3,2,'3name2')

insert into @abc values (3,3,'3name3')

insert into @abc values (3,6,'3name4')

select

distinct (select b.listcolumnid from @abc b

where b.listrowid = a.listrowid and b.listcolid = 1) column1,

(select c.listcolumnid from @abc c

where c.listrowid = a.listrowid and c.listcolid = 2)column2,

(select d.listcolumnid from @abc d

where d.listrowid = a.listrowid and d.listcolid = 3)column3,

(select e.listcolumnid from @abc e

where e.listrowid = a.listrowid and e.listcolid = 6)column4

from @abc as a

where a.listrowid = 1

union

select

distinct (select b.listcolumnid from @abc b

where b.listrowid = a.listrowid and b.listcolid = 1) column1,

(select c.listcolumnid from @abc c

where c.listrowid = a.listrowid and c.listcolid = 2)column2,

(select d.listcolumnid from @abc d

where d.listrowid = a.listrowid and d.listcolid = 3)column3,

(select e.listcolumnid from @abc e

where e.listrowid = a.listrowid and e.listcolid = 6)column4

from @abc as a

where a.listrowid = 2

union

select

distinct (select b.listcolumnid from @abc b

where b.listrowid = a.listrowid and b.listcolid = 1) column1,

(select c.listcolumnid from @abc c

where c.listrowid = a.listrowid and c.listcolid = 2)column2,

(select d.listcolumnid from @abc d

where d.listrowid = a.listrowid and d.listcolid = 3)column3,

(select e.listcolumnid from @abc e

where e.listrowid = a.listrowid and e.listcolid = 6)column4

from @abc as a

where a.listrowid = 3

--RESULT

column1column2column3column4

1name11name21name31name4

2name12name22name32name4

3name13name23name33name4

ARUN SAS

arun.sas SSChampion Points: 11831 More actions · Answer 2

hi,

your listcolid is more consent order then

this enough

select

distinct (select b.listcolumnid from @abc b

where b.listrowid = a.listrowid and b.listcolid = 1) column1,

(select c.listcolumnid from @abc c

where c.listrowid = a.listrowid and c.listcolid = 2)column2,

(select d.listcolumnid from @abc d

where d.listrowid = a.listrowid and d.listcolid = 3)column3,

(select e.listcolumnid from @abc e

where e.listrowid = a.listrowid and e.listcolid = 6)column4

from @abc as a

ARUN SAS

dastagirid SSCommitted Points: 1810 More actions · Answer 3

Thanks to send solution to my query.I felt happy.

But I have one problem.

I have data like below

venkat

2136548974

kadapa

AndraPradesh

India

giri

2136548974

kadapa

AndraPradesh

India

ram

2136548974

kadapa

AndraPradesh

India

nag

2136548974

kadapa

AndraPradesh

India

sreenu

2136548974

kadapa

AndraPradesh

India

nanda

2136548974

kadapa

AndraPradesh

India

subbu

2136548974

kadapa

AndraPradesh

India

I want data as shown bellow

column1 column2 column3 column4 column5

venkat 2136548974 kadapa AndraPradesh India

giri 2136548974 kadapa AndraPradesh India

ram 2136548974 kadapa AndraPradesh India

nag 2136548974 kadapa AndraPradesh India

sreenu 2136548974 kadapa AndraPradesh India

nanda 2136548974 kadapa AndraPradesh India

subbu 2136548974 kadapa AndraPradesh India

I need to create above structure dynamically.

I attached jpg file for your reference.

Thanks
Dastagiri.D

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi,

see again your ListcolumID remain static

USE SAME STATEMENT

ARUN SAS

dastagirid SSCommitted Points: 1810 More actions · Answer 5

thanks,

If ListColumnID is not static, what I have to do?

suppose ListCoumnID is increased.

please create table dynamically.

Thanks
Dastagiri.D

MNH SSCommitted Points: 1663 More actions · Answer 6

You can use the PIVOT relational operator to do this.

If the listColumnID is static you can use the following:

select listRowID,[1] as col1, [2] as col2,[3] as col3, [6] as col4

from

(SELECT listrowid, listcolumnid, listcolumnvalue

from @abc) As S

PIVOT

(

MIN(listColumnValue)

FOR listcolumnID IN ([1],[2],[3],[6])

) as P;

If the list columnID is not static, then use:

select listRowID,[1] as col1, [2] as col2,[3] as col3, [4] as col4

from

(

SELECT listrowid, listcolumnvalue, row_number() OVER(Partition by ListRowid order by ListRowId) as RowNum

from @abc

) As S

PIVOT

(

MIN(listColumnValue)

FOR RowNum IN ([1],[2],[3],[4])

) as P;

You can find more details for PIVOT at SQL BOL

http://msdn.microsoft.com/en-us/library/ms177410.aspx

dastagirid SSCommitted Points: 1810 More actions · Answer 7

Hi thanks

But in my database table all columns are not static.Means ListRowID, ListColumnID and ListColumnValue.

how can I write query?

Thanks
Dastagiri.D

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Heh... this is a pretty simple problem and I'd try to help but you keep posting data in that absolutely useless JPG format. I'm not going to copy from a graphic into a bunch of SELECT statements just to help. Please see the link in my signature for the proper way to post both sample data and a table to load it into. Thanks.

--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)