Problem changing the structure my table

Question

Problem changing the structure my table

cesarscorp2

SSC Enthusiast

Points: 166
More actions
March 3, 2014 at 10:28 am

#286304

Hello everybody! Can you help me? Actually my table have the structure of Table 1. I have problem when I try to change my structure of Table 1 in Table 2. I have a Store Procedure that It's OK but I need my table have the structure Table2 for my Store Procedure.
- TABLE 1 -
Name - LastName - DateBirthday - IdPerson
Jose - Perez - 12-02-1960 - PER3014
Jose - Perez - 12-02-1960 - PER5471
Maria - Lopez - 10-04-1950 - PER1047
Maria - Lopez - 10-05-1950 - PER3472
- TABLE 2 -
Name - LastName - DateBirthday - IdMainPerson - IdSecundaryPerson
Jose - Perez - 12-02-1960 - PER3014 - PER5471
Maria - Lopez - 10-04-1950 - PER1047 - PER3472
Do you have any idea for my problem?
Greetings

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

I am really not totally clear on what you want. But I am going to take a guess.

If you want the structure of table1, you can rename table2 and then perform a Select * Into. If you do not want to keep the data in Table2, then drop that table.

SELECT * INTO Table2

FROM Table1

WHERE 1=2

This will get the exact same structure that is in Table1, minus the constraints, indexes and data.

If this is not what you want, please clarify your explanation.

Andrew SQLDBA

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

I think he is trying to normalize table 1 more to table 2. It's normalization problem... What's the PK of table 1

--

SQLBuddy

mickyT SSChampion Points: 10360 More actions · Answer 3

Hi

This looks like a pivot question to me. I've made a couple of assumptions here that may be incorrect, so you may want to change it to suit.

Also have a look at this article Cross Tabs and Pivots, Part 1[/url] by Jeff Moden

-- Set up sample data

CREATE TABLE #Table1 (

Name varchar(10),

LastName varchar(10),

DateBirthday datetime,

IDPerson varchar(10));

INSERT INTO #Table1

VALUES

('Jose', 'Perez', '19601202', 'PER3014'),

('Jose', 'Perez', '19601202', 'PER5471'),

('Maria', 'Lopez', '19501004', 'PER1047'),

('Maria', 'Lopez', '19501004', 'PER3472'); -- changed birthdate as assumed they should be the same

-- Assumed that each person has only two IDs

WITH determineMain AS (

-- Without some other column to determine main and secondary, base it on ID

SELECT Name, LastName, DateBirthday, IDPerson, ROW_NUMBER() OVER (PARTITION BY Name, LastName, DateBirthday ORDER BY IDPerson) N

FROM #Table1

)

SELECT Name, LastName, DateBirthday, MIN(CASE WHEN n = 1 THEN IDPerson END) IDMainPerson, MIN(CASE WHEN n = 2 THEN IDPerson END) IDSecondaryPeron

FROM determineMain

GROUP BY Name, LastName, DateBirthday;

Hope this helps

cesarscorp2 SSC Enthusiast Points: 166 More actions · Answer 4

Thanks everybody and excuse me for my bad explication.

mickyT is just that I needed. Thanks a million! 🙂

mickyT SSChampion Points: 10360 More actions · Answer 5

cesarscorp2 (3/4/2014)
Thanks everybody and excuse me for my bad explication.
mickyT is just that I needed. Thanks a million! 🙂

No problem, glad to help