August 24, 2011 at 2:20 pm
I am trying to transpose some data i have been sent from a customer. Effectively column names need to become contents of fields in a new row. i have provided some information to help explain the scenario.
I need to go from this
D_AC D_RU D_SBU f00 H_EE H_EJ
501 208 90021 54 20 34
501 208 90001 44 10 34
501 208 90312 84 42 42
to this
D_AC D_RU D_SBU f00 D_Ent
502 208 90021 20 H_EE
502 208 90021 34 H_EJ
501 208 90001 10 H_EE
501 208 90001 34 H_EJ
501 208 90312 42 H_EE
501 208 90312 42 H_EJ
any ideas?
August 24, 2011 at 2:26 pm
What format are you receiving this data?
xml, csv, other?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 2:29 pm
Looking at the data again, it looks like they have concatenated the two fields using bit operations.
Do you have lookup tables that contain the values for those bits?
Specifically f00 and D_Ent
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 2:33 pm
the data comes in a csv format. I have no lookup tables. I just have the one file.
August 24, 2011 at 2:44 pm
Do you have the information on the values that each bit value represents?
Without it, you can't break that data up accurately.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 2:47 pm
I'm not sure if I overlook the obvious, but wouldn't UNPIVOT help here? (untested)
SELECT D_AC,D_RU,D_SBU,f00_new, D_Ent
FROM
(SELECT D_AC,D_RU,D_SBU,f00,H_EE,H_EJ
FROM OriginalTable) p
UNPIVOT
(f00_new FOR D_Ent IN
(H_EE,H_EJ)
)AS unpvt
August 24, 2011 at 2:51 pm
f00 represents an amount. H_EE and H_EY are entities which the f00 is split between. The d_ent is meant to be the new field which holds the name of the entity which in this case are H_EE and H_EY.
August 24, 2011 at 2:59 pm
I think the confusing part is reusing the f00 column name. In the original table it holds the total per D_SBU, whereas the expected output holds the splitted data.
August 24, 2011 at 3:05 pm
oh ok. i see what you mean. i will try this out and let you know how it goes. cheers
August 24, 2011 at 3:10 pm
LutzM (8/24/2011)
I think the confusing part is reusing the f00 column name. In the original table it holds the total per D_SBU, whereas the expected output holds the splitted data.
Nice catch.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2011 at 12:59 am
declare @tbl table (
D_AC int not null,
D_RU int not null,
D_SBU int not null,
f00 int not null,
H_EE int not null,
H_EJ int not null
);
insert @tbl(D_AC, D_RU, D_SBU, f00, H_EE, H_EJ)
select 501, 208, 90021, 54, 20, 34
union all select 501, 208, 90001, 44, 10, 34
union all select 501, 208, 90312, 84, 42, 42
select t.D_AC, t.D_RU, t.D_SBU, x.f00, x.D_Ent
from @tbl t
cross apply (
select t.H_EE as f00, 'H_EE' as D_Ent
union all select t.H_EJ as f00, 'H_EJ' as D_Ent
) x
August 25, 2011 at 1:06 am
Nice solution!
Avoids the ComputeScalar and Filter operation involved in the UNPIVOT statement...
August 25, 2011 at 4:09 am
How about if I have this table?
From this layout:
Hostname MACAddress
Computer1 MACAddress1
Computer1 MACAddress2
Computer2 MACAddress3
Computer3 MACAddress4
Computer4 MACAddress5
Computer4 MACAddress6
To this layout:
Hostname MACAddressA MACAddressB
Computer1 MACAddress1 MACAddress2
Computer2 MACAddress3 NULL
Computer3 MACAddress4 NULL
Computer4 MACAddress5 MACAddress6
Any help is greatly appreciated.
Thank you.
August 25, 2011 at 4:31 am
Please have a look at the CroosTab article referenced in my signature.
You'll need to add a row number per hostname in order to assign the address to either MacA or MacB.
As a side note:
Posting a new (and rather unrelated) question to a specific thread usually is considered as hijacking that thread since it may lead off topic and this may result in a unanswered original question.
Therefore, it is best practice to open a new thread with a new question.
Also, please have a look at the first link referenced in my signature on how to post ready to use sample data. Not only does it make it easier for us to work on the problem but you might also receive tested code specific to your question.
TIA.
August 25, 2011 at 8:53 pm
LutzM (8/25/2011)
Please have a look at the CroosTab article referenced in my signature.You'll need to add a row number per hostname in order to assign the address to either MacA or MacB.
As a side note:
Posting a new (and rather unrelated) question to a specific thread usually is considered as hijacking that thread since it may lead off topic and this may result in a unanswered original question.
Therefore, it is best practice to open a new thread with a new question.
Also, please have a look at the first link referenced in my signature on how to post ready to use sample data. Not only does it make it easier for us to work on the problem but you might also receive tested code specific to your question.
TIA.
That did it! 🙂
Thanks so much for your help and sorry for hijacking this thread
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply