October 15, 2009 at 11:20 pm
I have 3 tables:
CREATE TABLE [dbo].[profiles](
[ProfileID] [int] IDENTITY(1,1) NOT NULL,
[Nickname] [nvarchar](100) NULL,
[AccountID] [int] NULL,
[Email] [nvarchar](100) NULL,
[StatusID] [int] NULL,
[Comments] [nvarchar](1000) NULL,
[Completed] [bit] NULL CONSTRAINT [DF_profiles_Completed] DEFAULT ((0)),
[Submitted] [smalldatetime] NULL,
[DeptID] [int] NULL,
[TypeID] [int] NULL,
[Active] [bit] NULL,
[Visited] [varchar](500) NULL,
[Compiled] [varchar](500) NULL,
[GroupID] [int] NULL CONSTRAINT [DF_profiles_GroupID] DEFAULT ((0)),
[Updated] [bit] NULL CONSTRAINT [DF_profiles_Updated] DEFAULT ((0)),
CONSTRAINT [PK_profiles] PRIMARY KEY CLUSTERED
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO [profiles]([Nickname],[AccountID],[Email])
VALUES('aa',1,'aaa@h.cp,');
INSERT INTO [profiles]([Nickname],[AccountID],[Email])
VALUES('bbb',2,'bbb@h.com');
INSERT INTO [profiles]([Nickname],[AccountID],[Email])
VALUES('ccc',3,'ccc@h.com');
CREATE TABLE [dbo].[profiles_education](
[EducationID] [int] IDENTITY(1,1) NOT NULL,
[ProfileID] [int] NULL,
[Institution] [nvarchar](50) NULL,
[StartedMonth] [int] NULL,
[StartedYear] [int] NULL,
[EndedMonth] [int] NULL,
[EndedYear] [int] NULL,
[Major] [nvarchar](50) NULL,
[Minor] [nvarchar](50) NULL,
[DegreeID] [int] NULL CONSTRAINT [DF_profiles_education_DegreeID] DEFAULT ((0)),
[DegreeMonth] [int] NULL,
[DegreeYear] [int] NULL,
[GPA] [float] NULL CONSTRAINT [DF_profiles_education_GPA] DEFAULT ((0.0)),
CONSTRAINT [PK_applicants_education] PRIMARY KEY CLUSTERED
(
[EducationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(1,'UNIV1');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(1,'UNIV2');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(1,'UNIV3');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(1,'UNIV4');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(2,'UNIV11');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(2,'UNIV12');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(2,'UNIV13');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(2,'UNIV14');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(3,'UNIV111');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(3,'UNIV112');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(3,'UNIV113');
INSERT INTO [profiles_education]([ProfileID],[Institution])
VALUES(3,'UNIV114');
CREATE TABLE [dbo].[profiles_display](
[DisplayID] [int] IDENTITY(1,1) NOT NULL,
[ProfileID] [int] ,
[Nickname] [nvarchar](100) NULL,
[AccountID] [int] NULL,
[Institution1] [nvarchar](50) NULL,
[Institution2] [nvarchar](50) NULL,
[Institution3] [nvarchar](50) NULL,
[Institution4] [nvarchar](50) NULL)
I want to create a stored procedure to create the table profiles_display and insert into it Nicknames and their Universities given by institution1,institution2,...4. Rest of the columns can be left blank.
display_profiles should look something like this
Nickname Institution1 Institution2 Institution3 Institution4
aaa univ1 univ2 univ3 univ4
bbb univ11 univ12 univ13 univ14
ccc univ111 univ112 univ113 univ114
October 16, 2009 at 7:50 am
Hey,
Are you using SQL Server 2000 or SQL 2005 ?
Because the solution depends on that.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 16, 2009 at 7:57 am
bruhaspathy (10/16/2009)
Hey,Are you using SQL Server 2000 or SQL 2005 ?
Because the solution depends on that.
Thanks,
Bru Medishetty
Why does it depend ?, always willing to learn, your site lists you as a senior DBA 😛
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 8:03 am
I guess he can use PIVOT if the Version is SQL Server 2005 or higher and it would obviously not work if it is SQL Server 2000.
That was my intention to know.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 16, 2009 at 8:06 am
Well he did post it in the sql 2005 forums, but appreciate the clarification
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 8:11 am
Its my mistake that I have not seen that he posted in SQL Server 2005.
I just joined this site yesterday and thanks for letting me know, would not repeat the mistake.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 16, 2009 at 10:02 am
SQL SERVER 2005. Can someone show me how to do it ..Thanks
October 16, 2009 at 9:35 pm
Hi,
I know this is not the most optimum solution you can have for this scenario, may be you can use this for the time being.
TRUNCATE TABLE dbo.profiles_display ---- JUST TO ENSURE THERE IS NO DATA IN THAT TABLE
INSERT INTO dbo.profiles_display
Select TOP 3 B.ProfileID,NickName,NULL,B.Institution,NULL,NULL,NULL
from [dbo].[profiles] A
Inner Join [dbo].[profiles_education] B
ON A.ProfileID = B.ProfileID
group by B.ProfileID,NickName, B.Institution
CREATE TABLE #TEMP(
[NickName] [nvarchar](100) ,
[ProfileID] [int] ,
[Institution] [nvarchar](50) )
INSERT INTO #TEMP
Select A.NickName,B.ProfileID, B.Institution
from [dbo].[profiles] A
Inner Join [dbo].[profiles_education] B
ON A.ProfileID = B.ProfileID
DELETE FROM #TEMP WHERE Institution IN (SELECT DISTINCT(Institution1) FROM dbo.profiles_display)
UPDATE dbo.profiles_display SET Institution2 = DT.Institution
FROM dbo.profiles_display AA
INNER JOIN
(
Select NickName,ProfileID,Institution
FROM #TEMP
group by NickName,ProfileID,Institution
) DT
ON AA.NickName = DT.NickName
AND AA.ProfileID = DT.ProfileID
DELETE FROM #TEMP WHERE Institution IN (SELECT DISTINCT(Institution2) FROM dbo.profiles_display)
UPDATE dbo.profiles_display SET Institution3 = DT.Institution
FROM dbo.profiles_display AA
INNER JOIN
(
Select NickName,ProfileID,Institution
FROM #TEMP
group by NickName,ProfileID,Institution
) DT
ON AA.NickName = DT.NickName
AND AA.ProfileID = DT.ProfileID
DELETE FROM #TEMP WHERE Institution IN (SELECT DISTINCT(Institution3) FROM dbo.profiles_display)
UPDATE dbo.profiles_display SET Institution4 = DT.Institution
FROM dbo.profiles_display AA
INNER JOIN
(
Select NickName,ProfileID,Institution
FROM #TEMP
group by NickName,ProfileID,Institution
) DT
ON AA.NickName = DT.NickName
AND AA.ProfileID = DT.ProfileID
DELETE FROM #TEMP WHERE Institution IN (SELECT DISTINCT(Institution4) FROM dbo.profiles_display)
SELECT * FROM dbo.profiles_display
DROP TABLE #TEMP
I know this query would work for the exact scenario that you have mentioned, and if there are more institutions, then you need to keep on reuse the code n number of times as Institutions are there.
I tried PIVOT but I could not figure out the complete script, something is bugging and need to work out whats that.
Hope you find the solution good enough for time being.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 16, 2009 at 11:35 pm
PIVOT:
Select profileID,
[1] as Institution1,
[2] as Institution2,
[3] as Institution3,
[4] as Institution4
FROM (Select
ProfileID, Institution, ROW_NUMBER()
OVER (PARTITION BY PROFILEID ORDER BY ProfileID) as Row_no from
profiles_education) as PE
PIVOT (max(INSTITUTION) FOR Row_no in ([1],[2],[3],[4])) as PVT
Order by ProfileID
Cross tab:
Select ProfileID,
max(CASE WHEN Row_no =1 Then Institution ELSE NULL END),
max(CASE WHEN Row_no =2 Then Institution ELSE NULL END),
max(CASE WHEN Row_no =3 Then Institution ELSE NULL END),
max(CASE WHEN Row_no =4 Then Institution ELSE NULL END)
from (Select
*, ROW_NUMBER() OVER (PARTITION BY PROFILEID ORDER BY ProfileID) as Row_no from
profiles_education) PE
Group BY ProfileID
---------------------------------------------------------------------------------
October 16, 2009 at 11:51 pm
Thank a lot guys for your replies, but got some problems
Bru, the number of institutions won't change anytime sooner but new profiles will be added. Your solution doesn't work when i add a new profile to profiles table..
The same with the pivot solution...(also i would like to mention, i need to put the results into profiles_display not just show them)
October 17, 2009 at 12:27 am
fuzzy528 (10/16/2009)
The same with the pivot solution...(also i would like to mention, i need to put the results into profiles_display not just show them)
I dont think so!!! add this to your profile_education table
INSERT INTO [Profiles_education] ([ProfileID], [Institution])
Values(4,'UNIV1111')
INSERT INTO [Profiles_education] ([ProfileID], [Institution])
Values(4,'UNIV1112')
INSERT INTO [Profiles_education] ([ProfileID], [Institution])
Values(4,'UNIV1113')
INSERT INTO [Profiles_education] ([ProfileID], [Institution])
Values(4,'UNIV1114')
and then run the pivot query again, u will get what you want!
what matters here is the records in profile_education table.
We gave you the gist of how to do that... insert and your stored procedure should easily be built around this.
INSERT INTO [profiles_display](
ProfileID,
Institution1,
Institution2,
Institution3,
Institution4)
Select profileID,
[1] as Institution1,
[2] as Institution2,
[3] as Institution3,
[4] as Institution4
FROM (Select
ProfileID, Institution, ROW_NUMBER()
OVER (PARTITION BY PROFILEID ORDER BY ProfileID) as Row_no
from profiles_education) as PE
PIVOT (max(INSTITUTION) FOR Row_no in ([1],[2],[3],[4])) as PVT
Order by ProfileID
Select * from Profiles_Display
---------------------------------------------------------------------------------
October 17, 2009 at 12:49 am
Thanks a ton for your time brother !!
Last thing, I want the Nickname to be inserted as well into the profiles_display table. How can i do tht..
October 17, 2009 at 8:08 am
fuzzy528 (10/17/2009)
Thanks a ton for your time brother !!
Thats nice of you to say that 🙂 ur welcome.
Getting the nickname is simple, join your profile_education table with profiles table on profileID and get the nickname, see the bolded part.
Delete from Profiles_Display
INSERT INTO [profiles_display](
ProfileID,
NickName,
Institution1,
Institution2,
Institution3,
Institution4)
Select profileID,
NickName,
[1] as Institution1,
[2] as Institution2,
[3] as Institution3,
[4] as Institution4
FROM (Select
PE.ProfileID, P.NickName, Institution, ROW_NUMBER() OVER (PARTITION BY PE.PROFILEID ORDER BY P.ProfileID) as Row_no from
profiles_education PE JOIN
Profiles P ON PE.ProfileID = P.ProfileID ) as PE
PIVOT (max(INSTITUTION) FOR Row_no in ([1],[2],[3],[4])) as PVT
Order by ProfileID
Select * from Profiles_Display
Just another thing, read this post if you want to understand Pivot well. Its an excellent article by Jeff.
http://www.sqlservercentral.com/articles/T-SQL/63681/
---------------------------------------------------------------------------------
October 17, 2009 at 12:45 pm
Ok thanks a lot for sharing. Thanks again for your time.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply