Update

Hi ,
I try update my table.
I have 11 field and I want to compute in one field if not NULL.
but does not work.

      declare @counter int
declare @field  nvarchar(8);
set @field = 'field'
set @counter = 2
      while @counter < 13
      begin
      
      update #btype
       SET field =
    ( CASE
         WHEN (@field+cast(@counter as nvarchar) is not null) THEN field+','+@field
         
       END
)
samprgAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:

DECLARE @classes XML
SET @classes = '<Root>
  <Student Name="Mike">
    <Class Value="Math" />
    <Class Value="ESL" />
    <Class Value="Computer" />
    <Class Value="History" />
  </Student>
  <Student Name="Jime">
    <Class Value="Math" />
    <Class Value="Computer" />
  </Student>
</Root>';

;WITH classes AS (
SELECT r.s.value('../@Name', 'VARCHAR(100)') AS Name
     , r.s.value('@Value', 'VARCHAR(20)') AS Class
FROM @classes.nodes('//Student/Class') r(s)
)
SELECT Name AS "@Name"
     , (SELECT STUFF((SELECT ','+Class AS "text()"
	       FROM classes cc
		   WHERE cc.Name = c.Name
		   FOR XML PATH('')),1,1,'') AS "@Value"
		FOR XML PATH('Class'), TYPE)
FROM classes c
GROUP BY Name
FOR XML PATH('Student'), TYPE, ROOT('Root')
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Are you trying to get the value of an actual field? If so, what you are doing here is simply concatenating strings. Additionally, note, you can check for NULL using ISNULL() or COALESCE()...anyway, I don't believe you goal is to simply concatenate strings with the field name in it, so that is probably a moot point.
0
 
Kevin CrossChief Technology OfficerCommented:
You have a couple of approaches:
+Use UNPIVOT to get the values of the 11 columns in rows, then use FOR XML trick to get as a CSV in one column.
+Concatenate names of columns in a fashion that is COALESC(Col1,'') + COALESCE(','+Col2, '') ... then just use dynamic SQL.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
samprgAuthor Commented:
Great, In fact I neede in th above for xml, so can I put all classes in one column for every student
<Root>
<Student Name="Mike" >
    <CLass Value="Math" />
    <Class Value="ESL" />
    <Class Value="Computer" />
    <Class Value="History" />
  </Student>
<Student Name="Jime" >
    <CLass Value="Math" />
    <Class Value="Computer" />
   </Student>
<Roo/t>
0
 
Kevin CrossChief Technology OfficerCommented:
Something like this should work. As I stated, use UNPIVOT to get the columns into rows and then you can construct your XML from there. In this example, Classes is the name of your table, so replace that accordingly. Similarly, Class1, Class2, Class3, ..., Class12 are the name of the courses by Student. Change all fields appropriately.

;WITH Classes_Unpivoted(Student, ClassID, ClassName) AS (
   SELECT Student, ClassID, ClassName
   FROM Classes
   UNPIVOT (
      ClassName 
	  FOR ClassID IN (
	     Class1, Class2, Class3, Class4,
		 Class5, Class6, Class7, Class8,
		 Class9, Class10, Class11, Class12
      )
   ) upvt
) 
SELECT Student AS "@Name"
     , (SELECT ClassName AS "@Value" 
	 FROM Classes_Unpivoted c_u 
	 WHERE c_u.Student = c.Student
	 FOR XML PATH('Class'), TYPE)
FROM Classes c
FOR XML PATH('Student'), TYPE, ROOT('Root')
;

Open in new window


Result is:
<Root>
  <Student Name="Mike">
    <Class Value="Math" />
    <Class Value="ESL" />
    <Class Value="Computer" />
    <Class Value="History" />
  </Student>
  <Student Name="Jime">
    <Class Value="Math" />
    <Class Value="Computer" />
  </Student>
</Root>

Open in new window

0
 
samprgAuthor Commented:
We have mis-contact, sorry for that , I have already
<Root>
  <Student Name="Mike">
    <Class Value="Math" />
    <Class Value="ESL" />
    <Class Value="Computer" />
    <Class Value="History" />
  </Student>
  <Student Name="Jime">
    <Class Value="Math" />
    <Class Value="Computer" />
  </Student>
</Root>


and I need the result

<Root>
  <Student Name="Mike">
    <Class Value="Math" />
    <Class Value="ESL ,Computer, History" />
  </Student>
  <Student Name="Jime">
    <Class Value="Math, Computer" />
  </Student>
</Root>
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, so you are starting with the XML. I get you now.
0
 
Kevin CrossChief Technology OfficerCommented:

DECLARE @classes XML
SET @classes = '<Root>
  <Student Name="Mike">
    <Class Value="Math" />
    <Class Value="ESL" />
    <Class Value="Computer" />
    <Class Value="History" />
  </Student>
  <Student Name="Jime">
    <Class Value="Math" />
    <Class Value="Computer" />
  </Student>
</Root>';

;WITH classes AS (
SELECT r.s.value('../@Name', 'VARCHAR(100)') AS Name
     , r.s.value('@Value', 'VARCHAR(20)') AS Class
	 , ROW_NUMBER() 
          OVER(PARTITION BY r.s.value('../@Name', 'VARCHAR(100)') 
               ORDER BY (SELECT 1)) RN
FROM @classes.nodes('//Student/Class') r(s)
)
SELECT Name AS "@Name"
     , (SELECT "@Value"
	    FROM (
		   SELECT Class AS "@Value"
	       FROM classes cc
		   WHERE cc.Name = c.Name
		   AND cc.RN = c.RN
		UNION ALL
		   SELECT STUFF((SELECT ','+Class AS "text()"
	       FROM classes cc
		   WHERE cc.Name = c.Name
		   AND cc.RN <> c.RN
		   FOR XML PATH('')),1,1,'')
		) derived
		FOR XML PATH('Class'), TYPE)
FROM classes c
WHERE RN = 1
FOR XML PATH('Student'), TYPE, ROOT('Root')
;

Open in new window

0
 
samprgAuthor Commented:
good, the result is 98%, the result  hase 2 elements Class
look at

result :
<Root>
  <Student Name="Jime">
    <Class Value="Math" />
    <Class Value="Computer" />
  </Student>
  <Student Name="Mike">
    <Class Value="Math" />
    <Class Value="ESL,Computer,History" />
  </Student>
</Root>
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, sorry. I meant to post and ask why in the first case Math was isolated, but in the second it is combined. What is the pattern?
0
 
samprgAuthor Commented:
I am so sorry
the result is:
<Root>
  <Student Name="Mike">
    <Class Value="Math, ESL ,Computer, History" />
  </Student>
  <Student Name="Jime">
    <Class Value="Math, Computer" />
  </Student>
</Root>
0
 
samprgAuthor Commented:
Awesome, Thanks
0
 
samprgAuthor Commented:
Awesome
0
All Courses

From novice to tech pro — start learning today.