March 4, 2010 at 12:52 am
I've been meaning to investigate if we can convert data from one system to ours. The database, which I'm converting from, is kind of denormalized. I'm just working with one table, from that system, at the moment - we can call it TABLE_FROM.
It can look like this:
CREATE TABLE TABLE_FROM (
A1 varchar(100),
A2 varchar(100),
A3 varchar(100),
A4 varchar(100),
A5 varchar(100),
A6 varchar(100),
A7 varchar(100),
...
A100 varchar(100));
Each column is an answer to a question, i.e. a1 can be a social security number, a2 i gender and so forth.
Our system, which I’m converting to, the table looks a little different. A1, A2, A3 to A100 is all values in one column called ANSWERS.
What my problem is that I want to map the value A1 in our ANSWERS table to the column a1 in TABLE_FROM, and therefore get the value from the column in TABLE_FROM, i.e the social security number.
I tried this:
select col_name(OBJECT_ID('TABLE_FROM'), 1) from THE_SYSTEM_CONVERTING_FROM
where…
But this only returned the name of the column A1, not the value in that column.
CREATE TABLE TABLE_FROM
(
A1 VARCHAR(100),
A2 VARCHAR(100),
A3 VARCHAR(100),
A4 VARCHAR(100)
)
INSERT INTO TABLE_FROM VALUES ('65656515', 'Male', 'Allan', 'Iverson');
CREATE TABLE TABLE_TO
(
ANSWER VARCHAR(100),
RESULT VARCHAR(100)
)
INSERT INTO TABLE_TO VALUES ('A1', '');
INSERT INTO TABLE_TO VALUES ('A2','');
INSERT INTO TABLE_TO VALUES ('A3','');
INSERT INTO TABLE_TO VALUES ('A4','');
SELECT * FROM TABLE_TO;
In the RESULT column I would like to a have the column values from the TABLE_FROM table. It shoudl look like this in the TABLE_TO:
Row1: 'A1', '65656515'
Row2: 'A2', 'Male'
Row3: 'A3','Allan'
Row4: 'A4', 'Iverson'
Hope I made myself somewhat understandable.
I would really, REALLY, appreciate an answer to this… 🙂
March 4, 2010 at 3:00 am
Hi,
you could use a Tally-table to solve the problem:
CREATE TABLE #TABLE_FROM
(
ID INT,
A1 VARCHAR(100),
A2 VARCHAR(100),
A3 VARCHAR(100),
A4 VARCHAR(100)
)
INSERT INTO #TABLE_FROM VALUES (1, '65656515', 'Male', 'Allan', 'Iverson');
INSERT INTO #TABLE_FROM VALUES (2, '66554433', 'Male', 'John', 'Pearson');
CREATE TABLE #TABLE_TO
(
ANSWER VARCHAR(100),
RESULT VARCHAR(100)
)
SELECT TOP 1000 -- Need to be at least as high as the number of columns used
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
select ID, 'A' + CAST(N as varchar(10)) as ANSWER,
CASE N
WHEN 1 THEN A1
WHEN 2 THEN A2
WHEN 3 THEN A3
WHEN 4 THEN A4
END AS RESULT
from #TABLE_FROM
cross join #Tally
where N <= 4
order by ID, N
More info on Tally-tables: http://www.sqlservercentral.com/articles/T-SQL/62867/
/Markus
March 4, 2010 at 7:17 am
Just to add an alternative, if the number of columns is fixed:
DECLARE @TABLE_FROM
TABLE (
A1 VARCHAR(100),
A2 VARCHAR(100),
A3 VARCHAR(100),
A4 VARCHAR(100)
);
INSERT @TABLE_FROM
(A1, A2, A3, A4)
VALUES ('65656515', 'Male', 'Allan', 'Iverson');
SELECT U.ANSWER,
U.RESULT
FROM @TABLE_FROM
UNPIVOT (
RESULT
FOR ANSWER
IN (A1, A2, A3, A4)
) U;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2010 at 8:04 am
Thank you so much for you contribution! I thought about PIVOT and such, but I didn't really get it right. Both examples were really good, don't no which one to choose.
Well, now I have some converting to do... 🙂
March 4, 2010 at 5:33 pm
Either method will work fine, though the Tally solution is more flexible and generally performs a little faster. Some DBAs would prefer the UNPIVOT solution for its economical syntax. You should probably try both, and go with the one that seems 'best' to you - after all, you are the one that has to maintain it.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply