there is a table having two columns no and name
and the data is
1 A
2 B
3 C
write a query that will result a horizontal output
A,B,C
Answers were Sorted based on User's Feedback
Answer / utsav
declare @tst varchar(max)
set @tst=''
select @tst=@tst+col+',' from table
select @tst
there is a twist, if col contains any null value then this
query will return null. use isnull(col,'') in that case
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / kumar.t
1. First To create the following table.
Create table NewNew(sno int identity(1,1) primary key,
names varchar(100))
2. To INsert the record one by one
Insert Into NewNew Values ('A')
Insert Into NewNew Values ('B')
Insert Into NewNew Values ('C')
Insert Into NewNew Values ('D')
Insert Into NewNew Values ('F')
3. To view the records first
Select * From NEwNew
4. To create the following procedure
Create Proc New1
As
Begin
Declare @names varchar(100)
Declare @sQuery Varchar(7000)
Declare @sQuery1 Varchar(1000)
Declare @ans varchar(8000)
Declare @i int
Declare cur cursor for select names from newnew
Open cur
Set @i = 1
Set @sQuery1 = ''
Set @sQuery = ''
fetch next from cur into @names
While @@fetch_status=0
Begin
Set @sQuery1 = '(Select Top 1 names
from newnew where names in (select top '+ convert(varchar
(3),@i) +' names From newnew order by 1 Asc) order by 1
Desc) As names,'
Set @sQuery = @squery + @squery1
Set @i = @i + 1
Fetch next from cur into @names
End
Set @ans = Convert(varchar(6),'Select ') +
Left(@sQuery,Len(@sQuery)-1)
Exec (@ans)
Close cur
deallocate cur
End
5. To execute the procedure
now to view the output Ok
Exec New1
By
Kumar.T
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / miller
try using Pivot. for details follow the link
http://msdn.microsoft.com/en-us/library/ms177410.aspx link
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / rajesh.a
declare @res varchar(max)
set @res=''
select @res=@res+name+',' from table
select @res=substring(@res,1,len-1)
print @res
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / piyush sachan
DECLARE @List VARCHAR(8000)
SELECT @List = ISNULL(@List + ',', '') +names
FROM NewNew
SELECT @List
Orrrrrr
DECLARE @List VARCHAR(8000)
SELECT @List = COALESCE(@List + ',', '') +names
FROM NewNew
SELECT @List
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / senthil kumar murugan
;WITH ABC (no, name) AS
(
SELECT 1, CAST('' AS VARCHAR(8000))
UNION ALL
SELECT B.no + 1, B.name + A.name + ', '
FROM (
SELECT Row_Number() OVER (ORDER BY no) AS no, name FROM
emp1) A
INNER JOIN ABC B ON A.no = B.no
)
SELECT TOP 1 name FROM ABC ORDER BY no DESC
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / kumar.t
Hello Mr.B
In my table contain 100 record is there. at present what
you are doing?
Is This Answer Correct ? | 0 Yes | 2 No |
Answer / b
Select case when 1 then 'A'
when 2 then 'B'
when 3 then 'C' end as Name from tablename
Is This Answer Correct ? | 0 Yes | 3 No |
How to store and query spatial data?
What is query and its types?
What are the different types of collation sensitivity?
What is the order in which the SQL query is executed? list them in order.
5 Answers CarrizalSoft Technologies, CTS, Oracle,
Can binary strings be converted into numeric or float data types?
What are subqueries in sql server?
What is tablesample?
Display a roll having miminum marks in two subjects?
Which operator do you use to return all of the rows from one query except rows are returned in a second query?
i use few third party softwares. they r all having their own databases . but the data is repeated in all these databases - say a person is in all the three databases, but his name is stoared in diff format in all databases i want to create a centralised database ,and i dont want to re-enter the records . using the exisating records how can i build a centralised database?
Explain the properties of sub-query in sql server?
What is the difference between local table and global table
1 Answers CarrizalSoft Technologies, Polaris,