April 14, 2009 at 6:49 am
Hi all,
I have multiple record for one particular code in a table. I just want to convert one column values of that table as fields and another column values as values of above created fields.
Please someone help me.
Thanks,
Nitin
April 14, 2009 at 10:12 am
Please read this article[/url]. Then if you have specific questions post back here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2009 at 10:21 am
Jack is right. We need some sort of image to work from.
However, you will probably find examples similar to your problem if you search on "CONCATENATE", "CROSS TAB" and "PIVOT".
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 14, 2009 at 11:02 am
Can you give us Sample Input Data and sample out data and few lines about what do you want to do.
April 14, 2009 at 12:38 pm
Hi,
My data is in the following format:
Table1
Key field1 field2 field3
K001 1000 1500 800
K002 700 900 400
Table2
Key fields value
K001 field4 500
K001 field5 800
K001 field6 200
K002 field4 200
K002 field5 200
K002 field6 200
And now I want to merge it as in table below:
TempTable
Key field1 field2 field3 field4 field5 field6
K001 1000 1500 800 500 800 200
K002 700 900 400 200 200 200
Thanks
April 14, 2009 at 1:07 pm
This is a classic crosstab problem. You handle it by case expressions to test the value in the fields column to populate a column in the result set. Because we need only a single line per KEY value, we want to group by that column. Everything else we wrap in the aggregate function MAX, although we know there will only be one value per key.
Please let me know if you have any questions.
Bob
declare @Table1 table (Keyfld char(4), field1 int, field2 int, field3 int)
insert into @Table1
select 'K001', 1000, 1500, 800 union all
select 'K002', 700, 900, 400
declare @Table2 table (Keyfld char(4), fields varchar(10), value int)
insert into @Table2
select 'K001', 'field4', 500 union all
select 'K001', 'field5', 800 union all
select 'K001', 'field6', 200 union all
select 'K002', 'field4', 200 union all
select 'K002', 'field5', 200 union all
select 'K002', 'field6', 200
--select * from @Table1
--select * from @Table2
select t1.keyfld,max(t1.field1) as field1,max(t1.field2) as field2, max(t1.field3) as field3,
MAX(case when t2.fields = 'field4' then t2.value else null end) as field4,
MAX(case when t2.fields = 'field5' then t2.value else null end) as field5,
MAX(case when t2.fields = 'field6' then t2.value else null end) as field6
from @Table1 t1
join @Table2 t2 on t1.Keyfld = t2.Keyfld
group by t1.keyfld
order by t1.keyfld
Also, please note how I scripted the data you posted to actually populate two table variables so that I could test my solution. This practice was recommended in the article Jack referred to you. If you will set up your problems like this in the future, not only will it get you TESTED solutions quicker, but it will attract more volunteers to help you, and win you friends among them. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 14, 2009 at 10:58 pm
Thanks Bob,
Using your solution my problem is solved.
Nitin
April 15, 2009 at 8:23 am
Bob's solution is perfect.
You can even think about using PIVOT table on Table2 to convert rows into columns.
April 15, 2009 at 8:25 am
nitinkumar_tss (4/14/2009)
Hi,My data is in the following format:
Table1
Key field1 field2 field3
K001 1000 1500 800
K002 700 900 400
Table2
Key fields value
K001 field4 500
K001 field5 800
K001 field6 200
K002 field4 200
K002 field5 200
K002 field6 200
Thanks
I am curious to know why you have table in two different structures to store similar information (i.e. by looking at the data).
April 15, 2009 at 8:35 am
VK, I've seen this before in a couple of different contexts. In the absence of Sparse Columns, I've heard it proposed as a way to store "occasional" columns of data, without constantly expanding the schema of table 1. I've also seen a resemblance to table2 when a questionnaire or outside file has been imported into the database. And finally, I've seen this structure proposed as part of some misguided attempt to make an object-oriented database.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 15, 2009 at 8:40 am
You can even think about using PIVOT table on Table2 to convert rows into columns.
VK, try an experiment for me. Set up a PIVOT on Table 2, SET STATISTICS TIME ON, and compare PIVOT to the technique of using CASE Statements. You may need to increase the number of rows tested, but it would be interesting to see which runs faster. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply