Solved

Can some one do this with case statements

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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