Solved

Update

Posted on 2011-09-19
13
224 Views
Last Modified: 2012-05-12
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
Comment
Question by:samprg
  • 7
  • 6
13 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36564100
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36564131
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
 

Author Comment

by:samprg
ID: 36564659
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36564777
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
 

Author Comment

by:samprg
ID: 36564810
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36564828
Ah, so you are starting with the XML. I get you now.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36564876

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
 

Author Comment

by:samprg
ID: 36564884
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36564904
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
 

Author Comment

by:samprg
ID: 36564910
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36564945

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
 

Author Comment

by:samprg
ID: 36567124
Awesome, Thanks
0
 

Author Closing Comment

by:samprg
ID: 36567128
Awesome
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now