Solved

Update

Posted on 2011-09-19
13
233 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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