Solved

Create Complex View in SQL Server 2005

Posted on 2013-01-17
3
449 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
find SQL job run average duration 24 57
sql server cross db update 2 20
SQL Server Express automatically execute SQL or SP 8 37
Need some alteration to below mention query 2 12
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

733 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