results in horizontal line - SQL Server Forums

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

SQL Server Development (2000)

results in horizontal line

Author

Topic

tdave
Starting Member

3 Posts

Posted - 2009-02-13 : 13:36:21

i m getting results in vertical line and i want them in horizontal line...how can i get those..

i m getting like:
friends
other text
heart
due to reason
state

i want friends other text heart due to reason state

how can i get it

my sql statement is select * from sales (in that one column is salespart which iwant results in horizontal line)

thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:40:27

[code]DECLARE @result varchar(8000)
SELECT @result=coalesce(@result + ' ','') + youcol
FROM Yourtable

SELECT @result
[/code]

tdave
Starting Member

3 Posts

Posted - 2009-02-13 : 13:56:42

i have fname, lname, organization, type and date in one table

so

select fname,lname,org,type,date from table1 and type is the column name

example
fname,lname,org,type,date
---------------------------
abc xyz d001 friends 2/1/2009
abc xyz d001 other text 2/1/2009
abc xyz d001 heart 2/1/2009
abc xyz d001 due to reason 2/1/2009
hnd jhj d002 statewise 2/10/2009
hnd jhj d002 countrywise 2/10/2009

so i want results as

fname,lname,org,type, typeother, date
---------------------------
abc xyz d001 friends other text,heart,due to reason 2/1/2009
hnd jhj d002 statewise countrywise 2/10/2009

so the vertical values will be in another column - named type other.

is that possible??

tdave
Starting Member

3 Posts

Posted - 2009-02-13 : 15:04:59

i got all values friends,other text,heart,due to reason,statewise,countrywise all in one column

DECLARE @t varchar(4000)
SET @t = ''
SELECT @t = @t + ', ' + type From table1
SELECT STUFF(@t,1,2,'')

but i want only first value in one column and rest seperating by coma in another column.

fname,lname,org,type, typeother, date
---------------------------
abc xyz d001 friends other text,heart,due to reason 2/1/2009
hnd jhj d002 statewise countrywise 2/10/2009

do u have any idea?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 16:22:19

Please see this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119518

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 02:12:41

isnt this similar to

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119814

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 02:15:02

quote:
Originally posted by tdave

i got all values friends,other text,heart,due to reason,statewise,countrywise all in one column

DECLARE @t varchar(4000)
SET @t = ''
SELECT @t = @t + ', ' + type From table1
SELECT STUFF(@t,1,2,'')

but i want only first value in one column and rest seperating by coma in another column.

fname,lname,org,type, typeother, date
---------------------------
abc xyz d001 friends other text,heart,due to reason 2/1/2009
hnd jhj d002 statewise countrywise 2/10/2009

do u have any idea?

there's not concept of first or last unless you specify this based on another unique valued column. do you have any other unique valued column (like an identity) in your table?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-14 : 02:41:27

try like this
declare @temp table (fname VARCHAR(32),lname VARCHAR(32),org VARCHAR(32),type VARCHAR(32),date vARCHAR(32))
insert into @temp
select 'abc' ,'xyz' ,'d001' ,'friends', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'other text', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'heart', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'due to reason', '2/1/2009' union all
select 'hnd' ,'jhj' ,'d002' ,'statewise', '2/10/2009' union all
select 'hnd' ,'jhj' ,'d002' ,'countrywise', '2/10/2009'

Select identity(int,1,1)as rid , * into #temp from @temp

select *,(Select Count(*) from #temp
Where fname = Z.fname and rid <= Z.rid ) as Seq into #temp1 from #temp z

select * from #temp1

DECLARE @table TABLE ( namefirst varchar(32),Name VARCHAR(MAX),id1 varchar(32))

DECLARE @str VARCHAR(MAX),@id1 varchar(32),@str1 varchar(32)
SELECT @str = '',@str1=''

SELECT @ID1 = MIN(org) FROM #temp1

WHILE(@ID1 IS NOT NULL )
BEGIN

SELECT @str1 = type FROM #temp1 WHERE @id1= org and seq = 1
SELECT @STR = @STR + ','+ type FROM #temp1 WHERE @id1= org and seq <> 1

SELECT @str = STUFF(@str , 1 , 1,'')

INSERT INTO @TABLE SELECT @str1,@STR,@id1

SELECT @ID1 = MIN(org) FROM #temp1 WHERE org>@ID1

SELECT @str = '',@str1=''
END

SELECT namefirst,id1,name FROM @table

-- works in 2005 & above
select distinct
fname,
lname,
org,
substring((stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,1,'')),1,charindex(',',(stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,1,'')))-1) as type,
(stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,charindex(',',(stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,1,'')))+1,'')) as typeother,
date
from @temp tt

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-14 : 04:01:12

Try this also,


declare @temp table (fname VARCHAR(32),lname VARCHAR(32),org VARCHAR(32),type VARCHAR(32),date vARCHAR(32))
insert into @temp
select 'abc' ,'xyz' ,'d001' ,'friends', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'other text', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'heart', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'due to reason', '2/1/2009' union all
select 'hnd' ,'jhj' ,'d002' ,'statewise', '2/10/2009' union all
select 'hnd' ,'jhj' ,'d002' ,'countrywise', '2/10/2009'

declare @tempTable table ( id VARCHAR(32),string varchar(64))

	Declare @str VARCHAR(Max) , @str1 VARCHAR(Max),@str2 varchar(32),@str3 varchar(32)
	Select  @str = '',@str1 = '',@str2 = '',@str3 = ''

	Select @str = @str + ',' + type from @temp where fname in( 'abc')
	Select @str1 = type  From @temp Where fname = 'abc'
	Select @str2 = @str2 + ',' + type from @temp where fname in( 'hnd')
	Select @str3 = type  From @temp Where fname = 'hnd'

	Set @str = substring(@str,charindex(',',@str)+1,len(@str))
	set @str2 = Substring(@str2,charindex(',',@str2)+1,len(@str2))
Insert into @temptable
	select 'abc' as Id,@str union all
	select 'hnd' as Id,@str2 
	

select distinct t.id,t1.lname,t1.org,
substring(t.string,1,charindex(',',string)-1) as type,
substring(t.string,charindex(',',string)+1,len(string)) as TypeOther,
t1.date from @temptable t
inner join @temp t1 on t1.fname = t.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 04:02:55

quote:
Originally posted by bklr

try like this
declare @temp table (fname VARCHAR(32),lname VARCHAR(32),org VARCHAR(32),type VARCHAR(32),date vARCHAR(32))
insert into @temp
select 'abc' ,'xyz' ,'d001' ,'friends', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'other text', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'heart', '2/1/2009' union all
select 'abc' ,'xyz' ,'d001' ,'due to reason', '2/1/2009' union all
select 'hnd' ,'jhj' ,'d002' ,'statewise', '2/10/2009' union all
select 'hnd' ,'jhj' ,'d002' ,'countrywise', '2/10/2009'

Select identity(int,1,1)as rid , * into #temp from @temp

select *,(Select Count(*) from #temp
Where fname = Z.fname and rid <= Z.rid ) as Seq into #temp1 from #temp z

select * from #temp1

DECLARE @table TABLE ( namefirst varchar(32),Name VARCHAR(MAX),id1 varchar(32))

DECLARE @str VARCHAR(MAX),@id1 varchar(32),@str1 varchar(32)
SELECT @str = '',@str1=''

SELECT @ID1 = MIN(org) FROM #temp1

WHILE(@ID1 IS NOT NULL )
BEGIN

SELECT @str1 = type FROM #temp1 WHERE @id1= org and seq = 1
SELECT @STR = @STR + ','+ type FROM #temp1 WHERE @id1= org and seq <> 1

SELECT @str = STUFF(@str , 1 , 1,'')

INSERT INTO @TABLE SELECT @str1,@STR,@id1

SELECT @ID1 = MIN(org) FROM #temp1 WHERE org>@ID1

SELECT @str = '',@str1=''
END

SELECT namefirst,id1,name FROM @table

-- works in 2005 & above
select distinct
fname,
lname,
org,
substring((stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,1,'')),1,charindex(',',(stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,1,'')))-1) as type,
(stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,charindex(',',(stuff((select ','+type from @temp t where t.fname = tt.fname for xml path ('')),1,1,'')))+1,'')) as typeother,
date
from @temp tt

you cant guarantee that identity value will be generated in correct order by SELECT INTO ...statement

http://support.microsoft.com/kb/273586

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources