?
Solved

Can some one do this with case statements

Posted on 2011-03-10
4
Medium Priority
?
277 Views
Last Modified: 2012-05-11
View Question
[bottom]
Title:
SYbase
Zones:
Microsoft Development

MS SQL Server

Sybase Database

Tags:
Sybase

Points:
500

Question:
I have a table like this

Main_id            field_id      primary_id          data      row_id
3314            326                3314                   cal      1
3314            325                3314                 antonio      1
3314            326                3314                atlanta      2
3314            308                3314                  ohio      2
3314            326                3314                    67      3



Main_id      primary_id      field1      field2      field3      
3314      3314              cal      antonio      
3314      3314             atlanta            ohio
3314      3314                67            



I want the data in table 1 to be populated in table 2 based on the below logic

1) Here There are three distinct row_id's . So 3 rows should be inserted in table 2
2) In table1 the field_id's 308,325,326(rows) are  converted to columns in table2
   field id 326 valued are inserted in field1 , 325 in field2 and 308 in field 3 respectively
   
so the value 'cal' in 1 row in data column of table1 has row_id 1 , so in table 2 it should populate in 1st row in field1 because
its field id was 326

so the value 'atlanta' in 3rd  row in data column of table1  has row_id 2 , so in table 2 it should populate in 2nd row in field1 because
its field id was 326


so the value 'ohio' in 4th row in data column of table1  has row_id 2 , so in table 2 it should populate in 2nd row in field3 because
its field id is 308 in table1
 

I HAVE implemented the above logic in 3 update statements , due to the large data the performance  is slow .


Can this be done in 1 or 2 statements using case
0
Comment
Question by:vijay11
[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
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 total points
ID: 35103956
note : remove else ''  if you want NULL instead of empty string

select
	m.Main_id, m.primary_id,
	MAX(case field_id when 326 then m.data else '' end) field1,
	MAX(case field_id when 325 then m.data else '' end) field2,
	MAX(case field_id when 308 then m.data else '' end) field3
from
	(SELECT DISTINCT row_id FROM tablename) R
	INNER JOIN tablename m on m.row_id = r.row_id
group by
	m.mid, m.pid, m.row_id
order by
	m.row_id

Open in new window

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35103957
Please try this:

SELECT MAIN_ID, PRIMARY_ID, 
      [326] AS FIELD1, [325] AS FIELD2, [308] AS FIELD3
FROM (SELECT MAIN_ID, PRIMARY_ID, FIELD_ID, ROW_ID, DATA FROM YourTable) SRC
PIVOT (MAX(DATA) FOR FIELD_ID IN ([326],[325],[308])) AS PVT

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35104037
some modify

change

      (SELECT DISTINCT row_id FROM tablename) R
      INNER JOIN tablename m on m.row_id = r.row_id

to be only

      tablename m
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35107136
post your update statements and we will be better able to see what optimisations are possible...

please confirm which database system this is for and the particluar version/edtion
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

743 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