Help with a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a Query

Author

Topic

rpeacock
Starting Member

3 Posts

Posted - 2009-06-11 : 17:38:33

Hopefully this is a straight forward piece that I am just missing a step on somewhere but it is driving me crazy.

I have a table with two fields. One is Type and the other is Value. Each record has a one type and one value. Sample below.

Type - Value
FirstName - Smith
LastName - John
Company - ABC Corp
FirstName - Jane
LastName - Doe
Company - DEF Inc

I want to be able to write a query that will pull the information with the Type as the field name and the value as the field data.

Example:
Columns
FirstName, LastName, Company

Values
John, Smith, ABC Corp
Jane, Doe, DEF Inc
etc.

Any help would be appreciated.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-11 : 21:58:13

Since you only have two columns, there is no good way to know what first name goes with what last name. I am assuming it is every 3 records makes 1 group.

In order to do this I used a clustered update to create a groupID for every 3rd record.

One this I joined the table to itself in order to get the results.


Declare @MyTable table (ID int identity(1,1)Primary key clustered ,[type] varchar(30),[value] varchar(30), GroupID int)

Insert into @MyTable(Type,Value)
select 'FirstName' , 'Smith' Union all
select 'LastName' , 'John' Union all
select 'Company' , 'ABC Corp' Union all
select 'FirstName' , 'test' Union all
select 'LastName' , 'Johnson' Union all
select 'Company' , 'DBA Corp' Union all
select 'FirstName' , 'Jane' Union all
select 'LastName' , 'Doe' Union all
select 'Company' , 'DEF Inc' 

declare @MyCount int, @GroupID int,@Anchor int
Select @MyCount = 0,@GroupID = 0

Update a
set @Anchor = a.ID 
, @MyCount = case when @MyCount < 3 then @Mycount + 1 else 1 end
, @GroupID = a.GroupID = case when @MyCount = 1 then @GroupID + 1 else @GroupID end
from @MyTable a

select a.Value as FirstName,b.Value as LastName,c.Value  as Company
from @MyTable a
Inner Join
@MyTable b
on a.GroupID = b.GroupID
Inner Join
@MyTable c
on b.GroupID = c.GroupID
where 
	a.Type = 'Firstname'
and b.Type = 'Lastname'
and c.Type = 'Company'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

rpeacock
Starting Member

3 Posts

Posted - 2009-06-12 : 09:36:34

Sorry about that. I didn't make it real clear. I have a UserID field already in that table that is unique for which record goes with which. I got rushed to put this post together and forgot to put that on there. I know it makes a big difference.

Sorry about that and thanks for the help already. I think it can be even more simple, but I still can't get my head around it.

UserID is X for each record (John, FirstName), (Smith, LastName), etc
UserID is Y for each record (Jane, FirstName), (Doe, LastName), etc

Hope this makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 03:24:45

[code]SELECT MAX(CASE WHEN Type ='FirstName' THEN [Value] ELSE NULL END) AS FirstName,
MAX(CASE WHEN Type ='LastName' THEN [Value] ELSE NULL END) AS LastName,
MAX(CASE WHEN Type ='Company' THEN [Value] ELSE NULL END) AS Company
FROM YourTable
GROUP BY UserID
[/code]

rpeacock
Starting Member

3 Posts

Posted - 2009-06-22 : 17:24:58

That is perfect. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources