How it can be done..

Question

How it can be done..

amitsingh308

Ten Centuries

Points: 1287
More actions
April 8, 2011 at 10:51 am

#235311

Hii Every body
I retriving data from database and my out put is as follow?
%A
x1
x2
x3
%B
y1
y2
y3
%C
z1
z2
z3
and so on as above
but I want to show data as following menner
%A x1
%A x2
%A x3
same for %B and %C
how it can be possible please help me.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table def and sample data in a ready to use format as described in the first link in my signature. Furthermore, please include your expected result based on the sample together with what you've tried so far.

The information you've posted so far is rather vague...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 2

the table definition is

create table tbtemp (temp varchar(50))

My table contains only one column and information store in this table as follow

Hii Every body

I retriving data from database and my out put is as follow?

%A

x1

x2

x3

%B

y1

y2

y3

%C

z1

z2

z3

and I try to apply

SELECT a.tempset,b.tempset

FROM tbtem a

INNER JOIN tbtem b

ON a.tempset like '%%%'

and this

SELECT a.tempset,b.tempset

FROM tbtem a

LEFT JOIN tbtem b

ON a.tempset like '%%%' and b.tempset like '%^%%'

and this

SELECT a.tempset,b.tempset

FROM tbtem a

RIGHT JOIN tbtem b

ON a.tempset like '%%%' and a.tempset <>b.tempset

and this

SELECT a.tempset,b.tempset

FROM tbtem a

FULL JOIN tbtem b

ON a.tempset like '%%%' and b.tempset like '%^%%'

but I need output like as

%A X1

%A X2

%A X3

%B Y1

%B Y2

%B Y3

%C Z1

%C Z2

%C Z3

Patrick_Fiedler SSCommitted Points: 1904 More actions · Answer 3

Hi,

although this method is a little bit unusual for relational databases you can get your output by adding a identity column to your table.

create table tbtemp (orderid int identity(1,1), temp varchar(50))

So you can get the #-text above your row with following statement:

selectb.temp,

a.temp

fromtbtem a

inner join

tbtem b on b.temp like '[%]%'

and a.orderid > b.orderid

left join

tbtem c on c.temp like '[%]%'

and c.temp <> b.temp

and c.orderid between b.orderid and a.orderid

wherea.temp not like '[%]%'

and c.orderid is null

I hope it helps ... 🙂

Greets

Patrick Fiedler

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 4

Thank you its working but I can not modify table defination

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

amitsingh308 (4/8/2011)
Thank you its working but I can not modify table defination

Sorry for the bad news but there's nothing you can do because there's nothing in the table to guarantee the order of the rows. Sure, you'll find some code that looks like it works, but there's no guarantee that it'll work 100% of the time because, like I said, there's nothing to guarantee the order of the rows.

--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: 1004318 More actions · Answer 6

amitsingh308 (4/8/2011)
the table definition is
create table tbtemp (temp varchar(50))
My table contains only one column and information store in this table as follow
Hii Every body
I retriving data from database and my out put is as follow?
%A
x1
x2
x3
%B
y1
y2
y3
%C
z1
z2
z3
and I try to apply
SELECT a.tempset,b.tempset
FROM tbtem a
INNER JOIN tbtem b
ON a.tempset like '%%%'
and this
SELECT a.tempset,b.tempset
FROM tbtem a
LEFT JOIN tbtem b
ON a.tempset like '%%%' and b.tempset like '%^%%'
and this
SELECT a.tempset,b.tempset
FROM tbtem a
RIGHT JOIN tbtem b
ON a.tempset like '%%%' and a.tempset <>b.tempset
and this
SELECT a.tempset,b.tempset
FROM tbtem a
FULL JOIN tbtem b
ON a.tempset like '%%%' and b.tempset like '%^%%'
but I need output like as
%A X1
%A X2
%A X3
%B Y1
%B Y2
%B Y3
%C Z1
%C Z2
%C Z3

Please read the link that Lutz posted before you post another problem. The data you provided is simply not readily consumable and you'll get much better help when it is.

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 7

I will continue with PATRICK FIEDLER code

--create table #tmp(temp varchar(50)) --this table is your permanent table you remove this line

create table #tbtemp (orderid int identity(1,1), temp varchar(50))

--insert into #tmp values('%A')

--insert into #tmp values('x1')

--insert into #tmp values('x2')

--insert into #tmp values('x3')

--insert into #tmp values('%B')

--insert into #tmp values('y1')

--insert into #tmp values('y2')

--insert into #tmp values('y3')

--insert into #tmp values('%C')

--insert into #tmp values('z1')

--insert into #tmp values('z2')

--insert into #tmp values('z3')

insert into #tbtemp

select temp from tbtemp -- your permanent table name

select b.temp+' '+a.temp as result from #tbtemp a inner join

#tbtemp b on b.temp like '[%]%' and a.orderid > b.orderid left join

#tbtemp c on c.temp like '[%]%' and c.temp <> b.temp and c.orderid between b.orderid

and a.orderid where a.temp not like '[%]%' and c.orderid is null

--drop table #tmp

drop table #tbtemp

check this

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com