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
Solved

Can some one do this with case statements

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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