?
Solved

Create Complex View in SQL Server 2005

Posted on 2013-01-17
3
Medium Priority
?
451 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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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