Solved

Can some one do this with case statements

Posted on 2011-03-10
4
274 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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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