Prev: Need help writing stored procedure
Next: Did you ever get your Excel to MSSQL merge working?
From: Jeff Swanberg on 19 Jul 2010 12:55
Hi,

I need to insert data from a normalized table into a denormalized one and
wondered if there's a way to do it in the SELECT statement instead of using
a cursor (the way I'd normally do it)?

Table1 structured as:

STUDENTID int
GUARDIANID int
FIRSTNAME varchar(20)
LASTNAME varchar(40)
RELATION varchar (30)

This table currently holds multiple rows for each student id where each row
is a unique person that is a guardian of that student.

I need to move that data into the following structure:

STUDENTID int
GUARDIAN1NAME varchar(50)
GUARDIAN1RELATION varchar(30)
GUARDIAN2NAME varchar(50)
GUARDIAN2RELATION varchar(30)
GUARDIAN3NAME varchar(50)
GUARDIAN3RELATION varchar(30)
GUARDIAN4NAME varchar(50)
GUARDIAN4RELATION varchar(30)

There will only be 4 guardians allowed so any rows past 4 in the first table
will be dropped per student.

Without jumping on the "why would anyone do this??" bandwagon (it's not a
happy story), I'm just wondering if it's possible to do without using a
cursor to step through the rows of the first table.

js

From: SetonSoftware on 19 Jul 2010 13:38
On Jul 19, 12:55 pm, "Jeff Swanberg" <j_swanb...(a)msn.com> wrote:
> Hi,
>
> I need to insert data from a normalized table into a denormalized one and
> wondered if there's a way to do it in the SELECT statement instead of using
> a cursor (the way I'd normally do it)?
>
> Table1 structured as:
>
> STUDENTID int
> GUARDIANID int
> FIRSTNAME varchar(20)
> LASTNAME varchar(40)
> RELATION varchar (30)
>
> This table currently holds multiple rows for each student id where each row
> is a unique person that is a guardian of that student.
>
> I need to move that data into the following structure:
>
> STUDENTID int
> GUARDIAN1NAME  varchar(50)
> GUARDIAN1RELATION varchar(30)
> GUARDIAN2NAME  varchar(50)
> GUARDIAN2RELATION varchar(30)
> GUARDIAN3NAME  varchar(50)
> GUARDIAN3RELATION varchar(30)
> GUARDIAN4NAME  varchar(50)
> GUARDIAN4RELATION varchar(30)
>
> There will only be 4 guardians allowed so any rows past 4 in the first table
> will be dropped per student.
>
> Without jumping on the "why would anyone do this??" bandwagon (it's not a
> happy story), I'm just wondering if it's possible to do without using a
> cursor to step through the rows of the first table.
>
> js

Jeff

It seems you'll still need to loop through the rows but I don't see
where you'll need a cursor. They can almost ALWAYS be avoided. Try one
of the approaches described in this link:

http://searchsqlserver.techtarget.com/tip/Avoid-cursors-in-SQL-Server-with-these-methods-to-loop-over-records

Thanks

Carl
From: Eric Isaacs on 19 Jul 2010 14:57
Carl's suggestion of using a WHILE LOOP instead of a cursor is good,
but I can think of a few methods for how you could do this without a
cursor:

1) Using a PIVOT statement, you might be able to pivot the data into
the four guardian columns.

2) I would however opt to use ROW_NUMBER into a temporary table in
this case, because you only need the top 4 guardians for each
student. I would then take that temporary table and then LEFT JOIN
that table 4 times to itself with the ROW_NUMBER being 1 for the
first, 2 for the second, 3 for the third and 4 for the fourth, then
select each column from there. If there are a lot of students, you
may want to index that temporary table as well before joining it to
itself or just make a primary key that includes the StudentID and the
ROW_NUMBER column in that temporary table.

I'll post the SQL later if I have time.

-Eric Isaacs





From: Eric Isaacs on 19 Jul 2010 15:38
IF OBJECT_ID('tempdb..#G') IS NOT NULL
DROP TABLE #G

CREATE TABLE #G
(
STUDENTID INT,
GUARDIANID INT,
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(40),
RELATION VARCHAR(30)
)

INSERT INTO
#G
SELECT
1,
1,
'Joe',
'Fathers',
'Father'
UNION
SELECT
1,
2,
'Martha',
'Fathers',
'Step-Mother'
UNION
SELECT
1,
3,
'Ma',
'Maiden',
'Mother'
UNION
SELECT
2,
12,
'David',
'Green',
'Father'
UNION
SELECT
2,
13,
'Sue',
'Green',
'Mother'


IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T

CREATE TABLE #T
(
STUDENTID INT,
GUARDIANNUMBER INT,
GUARDIANID INT,
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(40),
RELATION VARCHAR(30)
)


INSERT INTO
#T
(
STUDENTID,
GUARDIANNUMBER,
GUARDIANID,
FIRSTNAME,
LASTNAME,
RELATION
)
SELECT
STUDENTID,
ROW_NUMBER() OVER (PARTITION BY STUDENTID ORDER BY RELATION)
AS GUARDIANNUMBER,
GUARDIANID,
FIRSTNAME,
LASTNAME,
RELATION
FROM
#G

SELECT
*
FROM
#T


IF OBJECT_ID('tempdb..#R') IS NOT NULL
DROP TABLE #R

CREATE TABLE #R
(
STUDENTID INT,
GUARDIAN1NAME VARCHAR(50),
GUARDIAN1RELATION VARCHAR(30),
GUARDIAN2NAME VARCHAR(61),
GUARDIAN2RELATION VARCHAR(30),
GUARDIAN3NAME VARCHAR(61),
GUARDIAN3RELATION VARCHAR(30),
GUARDIAN4NAME VARCHAR(61),
GUARDIAN4RELATION VARCHAR(30)
)


INSERT INTO
#R
SELECT DISTINCT --Note, you don't need distinct if you use the
STUDENT table instead of the Student-Guardian table as the first table
#G.STUDENTID,
T1.FIRSTNAME + ' ' + T1.LASTNAME AS GUARDIAN1NAME,
T1.RELATION AS GUARDIAN1RELATION,
T2.FIRSTNAME + ' ' + T2.LASTNAME AS GUARDIAN2NAME,
T2.RELATION AS GUARDIAN2RELATION,
T3.FIRSTNAME + ' ' + T3.LASTNAME AS GUARDIAN3NAME,
T3.RELATION AS GUARDIAN3RELATION,
T4.FIRSTNAME + ' ' + T4.LASTNAME AS GUARDIAN4NAME,
T4.RELATION AS GUARDIAN4RELATION
FROM
#G --Use Student table here instead and drop distinct.
LEFT JOIN #T AS T1 ON #G.STUDENTID = T1.STUDENTID
AND T1.GUARDIANNUMBER = 1
LEFT JOIN #T AS T2 ON #G.STUDENTID = T2.STUDENTID
AND T2.GUARDIANNUMBER = 2
LEFT JOIN #T AS T3 ON #G.STUDENTID = T3.STUDENTID
AND T3.GUARDIANNUMBER = 3
LEFT JOIN #T AS T4 ON #G.STUDENTID = T4.STUDENTID
AND T4.GUARDIANNUMBER = 4


SELECT
*
FROM
#R


-Eric Isaacs
From: Erland Sommarskog on 19 Jul 2010 17:56
Jeff Swanberg (j_swanberg(a)msn.com) writes:
> This table currently holds multiple rows for each student id where each
> row is a unique person that is a guardian of that student.
>
> I need to move that data into the following structure:
>
> STUDENTID int
> GUARDIAN1NAME varchar(50)
> GUARDIAN1RELATION varchar(30)
> GUARDIAN2NAME varchar(50)
> GUARDIAN2RELATION varchar(30)
> GUARDIAN3NAME varchar(50)
> GUARDIAN3RELATION varchar(30)
> GUARDIAN4NAME varchar(50)
> GUARDIAN4RELATION varchar(30)
>
> There will only be 4 guardians allowed so any rows past 4 in the first
> table will be dropped per student.

Yes, you can do this without a cursor. This solution requires SQL 2005 or
later:

WITH numbered AS (
SELECT STUDENTID, GUARDIANID, FIRSTNAME + ' ' + LASTNAME AS name,
RELATION,
rowno = row_number() OVER (PARTITION BY STUDENTID
ORDER BY GUARDIANID)
FROM Table1
)
SELECT STUDENTID,
GUARDIAN1NAME = MAX(CASE rowno WHEN 1 THEN name END),
GUARIDAN1RELATION = MAX(CASE rowno WHEN 1 THEN RELATION END),
GUARDIAN2NAME = MAX(CASE rowno WHEN 2 THEN name END),
GUARIDAN2RELATION = MAX(CASE rowno WHEN 2 THEN RELATION END),
GUARDIAN3NAME = MAX(CASE rowno WHEN 3 THEN name END),
GUARIDAN3RELATION = MAX(CASE rowno WHEN 3 THEN RELATION END),
GUARDIAN41NAME = MAX(CASE rowno WHEN 4 THEN name END),
GUARIDAN5RELATION = MAX(CASE rowno WHEN 4 THEN RELATION END)
FROM numbered
GROUP BY STUDENTID



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Need help writing stored procedure
Next: Did you ever get your Excel to MSSQL merge working?