Solved

Create Complex View in SQL Server 2005

Posted on 2013-01-17
3
450 Views
Last Modified: 2013-01-17
Greetings Experts!

I am trying to wrap my head around a issue I have not encountered before.

I have a table with the following structure:

fk_E01_01 (FK, int, not null)
NAME (varchar(50), not null)
VALUE (varchar(30), not null)

This results in a table like the one below

fk_E01_01   |   Name                  |   VALUE
-------------------------------------------------------------
123457        |   Trauma                |   No Activation
123458        |   Key                      |   102
123458        |   Section                 |   15
123458        |   CardNumber        |   17
123458        |   Level                    |   A
123458        |   Determinant         |   02
123459        |   Key                      |   170
123459        |   Section                |   20
123459        |   Quarter Section   |   A
123459        |   Trauma                |   Activation
123460…..
     
What I need is a view that concatenates the value for “Key” and “Section” on fk_E01_01 to result like this:

fk_E01_01   |     MapKey
-----------------------------------
123458        |   102-15
123459        |   170-20
123460…..

I have very little SQL training and this is beyond me.

Using SQL Server 2005

Thanks,

Task
0
Comment
Question by:taskhill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 38788353
Try

select
fk_E01_01
NAME +VALUE
from table
group by   fk_E01_01,NAME +VALUE
0
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 38788381
Is there always only one Name value (Key or Section) for each fk? If so, this should work:

CREATE VIEW MyView
AS
SELECT A.fk_E01_01, A.[Value] + '-' + B.[Value] AS MapKey
FROM 
	(SELECT fk_E01_01, [value]
	FROM MyTable
	WHERE [Name] = 'Key') A
INNER JOIN 
	(SELECT fk_E01_01, [value]
	FROM MyTable
	WHERE [Name] = 'Section') B
ON A.fk_E01_01 = B.fk_E01_01

Open in new window

0
 

Author Closing Comment

by:taskhill
ID: 38788425
Flawless!!!!  Thank you!
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question