Solved

Update

Posted on 2011-09-19
13
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 60

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 60

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 60

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 60

Expert Comment

by:Kevin Cross
ID: 36564828
Ah, so you are starting with the XML. I get you now.
0
 
LVL 60

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 60

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 60

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

734 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