?
Solved

Can some one do this with case statements

Posted on 2011-03-10
4
Medium Priority
?
280 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
  • 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

621 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