Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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
)
0
samprg
Asked:
samprg
  • 7
  • 6
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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
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
 
samprgAuthor Commented:
Awesome, Thanks
0
 
samprgAuthor Commented:
Awesome
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now