Display column values in a table in a single row. RSS

• • Reply
  

  minakshi_23

  Member

  116 Points

  40 Posts

  Display column values in a table in a single row.

  May 06, 2011 10:48 AM|LINK

  Hi All,

   I want to display keys column in the below table

   

  dbo.temp

  keys

  --------

  vitA, vitB

  vitB

  vitC

  Protein,VitA,VitB

   

  in a single row given below

  vitA, vitB,vitB,vitC,Protein,VitA,VitB

  Thanks

• • Reply
  

  Prince Sriva...

  Contributor

  2290 Points

  419 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 10:52 AM|LINK

  USe this

   

    select stuff((
          SELECT ',' + cast(fieldname as varchar(20))
          FROM tablename
          FOR XML PATH('')
          ),1,1,'')  as AMultiplex

   

   

  If this will help you please mark as answer
  My Blog
  ROHIT KUMAR SRIVASTAVA
  • Marked as answer by minakshi_23 on May 09, 2011 09:53 AM
• • Reply
  

  minakshi_23

  Member

  116 Points

  40 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 10:59 AM|LINK

  That worked! Cheers!

  I also want to count the occurences of each word in that row.

  Thanks

   

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:00 AM|LINK

  Declare @Table Table
  (
  	Id int,
  	Original varchar(50)
  )
  insert into @Table
  Select 1,'String1'
  Union All
  Select 1,'String2'
  Union All
  Select 1,'String3'
  Union All
  Select 2,'String4'
  Union All
  Select 2,'String5'
  
  
  Select (
  
  select stuff(
  (
  	SELECT ',' + Original
      FROM @Table
      FOR XML PATH('')
  ),1,1,'')
  ) As KeyVal,COUNT(Id) As TotalWordCount
  From @Table

  Yogesh Bhadauriya
  .Net All About
  
  • Edited by yrb.yogi on May 06, 2011 11:02 AM
• • Reply
  

  Bhaarat

  Contributor

  3403 Points

  850 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:03 AM|LINK

  select REPLACE(REPLACE(replace((select ''+ t.Empname from @t t for xml  PATH('i'))
  ,'</i><i>',','),'<i>',''),'</i>','') as Empname

  Bhaarat
  Remember to click "Mark as Answer" on the post that helps U
  http://ransandeep.blogspot.com
• • Reply
  

  Prince Sriva...

  Contributor

  2290 Points

  419 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:08 AM|LINK

  Hello minakshi

  use a varable in Sql server and all this record in this valrable

  and use the string function and split it into after Quamma

  and return it

  if this one help u please mark as answer

  Thanks

  Rohit

  If this will help you please mark as answer
  My Blog
  ROHIT KUMAR SRIVASTAVA
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:14 AM|LINK

  minakshi_23

  That worked! Cheers!

  I also want to count the occurences of each word in that row.

  Thanks

   

  To find  the occurence of each word in each row please create the following table valued function in database

   

  Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
  returns @temptable TABLE (items varchar(8000))
  as
  begin
  	declare @idx int
  	declare @slice varchar(8000)
      
  	select @idx = 1
  		if len(@String)<1 or @String is null  return
      
  	while @idx!= 0
  	begin
  		set @idx = charindex(@Delimiter,@String)
  		if @idx!=0
  			set @slice = left(@String,@idx - 1)
  		else
  			set @slice = @String
  		
  		if(len(@slice)>0)
  			insert into @temptable(Items) values(@slice)
  
  		set @String = right(@String,len(@String) - @idx)
  		if len(@String) = 0 break
  	end
  return
  end

   

  After that Call below query, here i have covered all aspect probabily.

   

   

  Declare @Table Table
  (
  	Id int,
  	Original varchar(50)
  )
  insert into @Table
  Select 1,'vitA,vitB'
  Union All
  Select 2,'vitB'
  Union All
  Select 3,'vitC'
  Union All
  Select 4,'Protein,VitA,VitB'
  
  
  Select Id,Original,COUNT(items) As RowWordCount ,
  (
  	select stuff(
  	(
  			SELECT ',' + Original
  		FROM @Table
  		FOR XML PATH('')
  	),1,1,'') ) As KeyVal
  From @Table
  Cross Apply dbo.Split(Original,',')
  Group by Id,Original
  order by Id
  
  ---output
  ---------------------------------------------------------------
  --Id	Original	RowWordCount	KeyVal
  --1	vitA,vitB	2	vitA,vitB,vitB,vitC,Protein,VitA,VitB
  --2	vitB	1	vitA,vitB,vitB,vitC,Protein,VitA,VitB
  --3	vitC	1	vitA,vitB,vitB,vitC,Protein,VitA,VitB
  --4	Protein,VitA,VitB	3	vitA,vitB,vitB,vitC,Protein,VitA,VitB
  

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  minakshi_23

  Member

  116 Points

  40 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:19 AM|LINK

  How can I list the row back to a column ?

  vitA, vitB, vitA, protein, vitB,vitA

  to a column

  vit A

  vitB

  vitA

  protein

  vitB

  vitA

   

  Thanks

   

• • Reply
  

  minakshi_23

  Member

  116 Points

  40 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:25 AM|LINK

  That's not how I want to count the occurences of each word in a row.

   

  example

  (row)

  vitA,vitB,vitA,vitB,vitA,protein,vitC

  and this is how I want to display and count their occurences.

  vitA            vitB          protein        vitC

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

  3               2              1                   1

  Hope this makes some sense.

  thanks

   

   

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Display column values in a table in a single row.

  May 06, 2011 11:26 AM|LINK

  minakshi_23

  How can I list the row back to a column ?

  vitA, vitB, vitA, protein, vitB,vitA

  Using the split table valued function which was posted by me in earlier post.

  please check this query.

  select * from dbo.Split('vitA, vitB, vitA, protein, vitB,vitA',',')
  
  --output
  ------------------
  --items
  --vitA
  -- vitB
  -- vitA
  -- protein
  -- vitB
  --vitA

  Yogesh Bhadauriya
  .Net All About