sp needed

• rpatil22

  Ten Centuries

  Points: 1371

  More actions

  January 7, 2010 at 2:32 pm

  #215466

  Guys I will receive a string

  'name',a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4

  i want to make data in table

  name a1 b1 c1

  name a2 b2 c2

  name a3 b3 c3

  name a4 b4 c4

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 7, 2010 at 3:08 pm

  #1100539

  This will take several steps:

  Step 1: convert the string into a table

  Step 2: separate the value of the first item

  Step 3: Build a cross tab (see the related link in my sgnature for details).

  Please note that the string split function I use will return a table with two columns(ItemOrder and Item). You'll find a number of solutions for string split functions around here.

  Side note: I assume you'll always have the same number of culomns. If not, you'd need to use DynamicCrossTab (see the related link in my signature).

  DECLARE @STR varchar(50)

  SET @STR='name,a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4'

  ;with split as

  (

  SELECT *

  FROM dbo.delimitedsplit (@str,',')

  ),

  cte1 AS

  (

  SELECT item i1 FROM split WHERE itemid=1

  )

  ,cte2 as

  (

  SELECT

  i1,

  item,

  CASE WHEN itemid<>1 THEN left(item,1) ELSE NULL END AS col1,

  CASE WHEN itemid<>1 THEN right(item,1) ELSE NULL END AS col2

  FROM split

  CROSS apply cte1

  WHERE split.itemid>1

  )

  SELECT

  i1,

  max(CASE WHEN col1='a' THEN item ELSE NULL END) AS a,

  max(CASE WHEN col1='b' THEN item ELSE NULL END) AS b,

  max(CASE WHEN col1='c' THEN item ELSE NULL END) AS c

  FROM cte2

  GROUP BY i1,col2

  /* result set

  i1abc

  namea1b1c1

  namea2b2c2

  namea3b3c3

  namea4b4c4

  */

  ---

  
  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]

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  January 7, 2010 at 3:21 pm

  #1100552

  rpatil22 (1/7/2010)

  ---

  Guys I will receive a string

  'name',a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4

  i want to make data in table

  name a1 b1 c1

  name a2 b2 c2

  name a3 b3 c3

  name a4 b4 c4

  Take a look at the following...

  http://www.sqlservercentral.com/articles/T-SQL/63003/

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

• José.Cruz

  Right there with Babe

  Points: 722

  More actions

  January 8, 2010 at 2:49 am

  #1100757

  Hi,

  Here's another way of doing what you want, with the result being a litle bit more dynamic

  create procedure sp_test_split(@text varchar(100), @numfields int)

  as

  create TABLE #fields (

  ointidentity,

  idint,

  fvarchar(10)

  )

  declare @sql nvarchar(max)

  declare @index int

  declare @numfield int

  set @numfield = -1

  if right(@text, 1) <> ','

  set @text = @text + ','

  set @index = 10

  while @index > 0

  begin

  select @index = charindex(',', @text)

  if @index > 0

  begin

  insert into #fields (id, f)

  select case @numfield when -1 then -1 else @numfield/@numfields end, replace(substring(@text, 0, @index), '''', '')

  set @numfield = @numfield + 1

  select @text = substring(@text, @index + 1, len(@text))

  if @numfield < @numfields

  set @sql = coalesce(@sql + ',[', '[') + cast(@numfield + 1 as varchar) + ']'

  end

  end

  set @sql = ';with cteData as (

  select f1.f as mf, f2.id, f2.f, row_number() over(partition by f1.f , f2.id ORDER BY f2.o) level

  from #fields f1, #fields f2

  where f1.id = -1

  and f2.id <> -1

  )

  select mf, ' + @sql + '

  from (

  select level, mf, f, id

  from ctedata

  ) as tbl

  pivot(

  max(f)

  for level in (' + @sql + ')

  ) as pt'

  exec sp_executesql @sql

  And you can use it this way:

  exec sp_test_split '''name'',a1,b1,c1,d1,a2,b2,c2,d2,a3,b3,c3,d3,a4,b4,c4,d4', 4

  José Cruz

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 8, 2010 at 6:25 am

  #1100805

  rpatil22 (1/7/2010)

  ---

  Guys I will receive a string

  'name',a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4

  i want to make data in table

  name a1 b1 c1

  name a2 b2 c2

  name a3 b3 c3

  name a4 b4 c4

  Not nearly enough information - but enough to have a little play:

  /*

  SELECT TOP 100

  IDENTITY(INT,1,1) AS n

  INTO #Numbers

  FROM Master.dbo.SysColumns sc1,

  Master.dbo.SysColumns sc2

  */

  DECLARE @String VARCHAR(MAX)

  SET @String = '''name'',a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4'

  SELECT

  REPLACE(LEFT(@String, CHARINDEX(',', @String)-1), '''', '') AS Col1,

  SUBSTRING(@String, n1+1, n2-(n1+1)) AS Col2,

  SUBSTRING(@String, n2+1, n3-(n2+1)) AS Col3,

  SUBSTRING(@String, n3+1, n4-(n3+1)) AS Col4

  FROM (

  SELECT

  n AS n1,

  CHARINDEX (',', @String, n+1) AS n2,

  CHARINDEX (',', @String, CHARINDEX (',', @String, n+1)+1) AS n3,

  ISNULL(NULLIF(CHARINDEX (',', @String, CHARINDEX (',', @String, CHARINDEX (',', @String, n+1)+1)+1), 0), LEN(@String)+1) AS n4

  FROM #Numbers n

  WHERE n IN (7,16,25,34)

  ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden