Solved

SQL rows

Posted on 2013-06-19
10
164 Views
Last Modified: 2013-06-20
I have data returned as follows from a SQL:
 
10198      500      PLDept      A      3      1      1      Process Level      A      100
10198      500      PLDept      A      4      1      2      Department      A      10092
10230      500      PLDept      A      3      1      1      Process Level      A      100
10230      500      PLDept      A      4      1      2      Department      A      85001
10230      501      PLDept      A      4      1      2      Department      A      85001
10230      501      PLDept      A      3      1      1      Process Level      A      210
10245      500      PLDept      A      3      1      1      Process Level      A      210
10245      500      PLDept      A      4      1      2      Department      A      20110

The columns with Process Level/Department and values of 100/10092/100/85001 etc. reside in fields, ELEMENTNAME and ELEMENTVALUE respectively.  With SQL how can I create a separate column for ELEMENTNAME as well as create a separate column for the ELEMENTVALUE?

I tried a subquery and was not able to generate separate columns as well as a UNION.
0
Comment
Question by:garyjgs
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 9

Assisted Solution

by:selva_kongu
selva_kongu earned 200 total points
ID: 39260356
You can use a CROSS APPLY to unpivot the data:
0
 
LVL 9

Assisted Solution

by:selva_kongu
selva_kongu earned 200 total points
ID: 39260373
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 39260410
What was the sql that returned the data you have shown and what would you like it to look like?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:garyjgs
ID: 39260466
Here is the SQL:
SELECT
 R.RULENAME AS EMPLOYEE
,T.ELEMENTID
,E.ELEMENTNAME
,V.ELEMENTVALUE1
                                       
FROM LAWSONRS.dbo.ERS_STRUCTURE AS S
     INNER JOIN LAWSONRS.dbo.ERS_STRUCTURETEMPLATE AS T
     ON T.STRUCTUREID = S.STRUCTUREID
                     
     LEFT OUTER JOIN LAWSONRS.dbo.ERS_STRUCTUREMAPPING AS M
     ON M.STRUCTUREID = S.STRUCTUREID
                     
     INNER JOIN LAWSONRS.dbo.ERS_ELEMENTS AS E
     ON E.ELEMENTID = T.ELEMENTID
                     
     INNER JOIN LAWSONRS.dbo.ERS_RULEDETAIL AS D
     ON D.STRUCTUREID = S.STRUCTUREID
     AND D.ELEMENTID = E.ELEMENTID

     INNER JOIN LAWSONRS.dbo.ERS_RULEVALUES AS V
     ON V.STRUCTUREID = S.STRUCTUREID
     AND V.RULEGROUPING=D.RULEGROUPING
     AND V.ELEMENTID = E.ELEMENTID
     AND V.RULEID = D.RULEID
                       
     INNER JOIN LAWSONRS.dbo.ERS_RULEMASTER AS R
     ON R.RULEID=V.RULEID
     
     ORDER BY RULENAME

I would like the result to be:
EMPLOYEE ELEMENTID ELEMENTNAME ELEMENTVALUE1                  
10198        3                   Process Level      100                           4      Department      10092
10230        3                   Process Level      100                           4      Department      85001
10230        3                   Process Level      210                           4      Department      85001
10245        3                   Process Level      210                           4      Department      20110
10262        3                   Process Level      230                           4      Department      23101
10262        3                   Process Level      230                           4      Department      23001
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 39261531
I'm not sure I follow your request.

Process Level/Department resides in field [ELEMENTNAME]
values of 100/10092/100/85001 in field [ELEMENTVALUE]

so they are already "separate"
>>With SQL how can I create a separate column[s] for ELEMENTNAME ... ELEMENTVALUE?

You then present a query (immediately above) that has 4 columns ( referencing a different field name [V.ELEMENTVALUE1] ) but then display an expected result that appears to be 7 columns (but has 4 headings).

Are some of those 4 fields "compound" strings, and what you want to do is "break them up" into smaller parts? (like X? Y? and Z? underneath).

EMPLOYEE ELEMENTID ELEMENTNAME             ELEMENTVALUE1                  
10198   |  3      |Process Level      100 |4      Department      10092
10230   |  3      |Process Level      100 |4      Department      85001
10230   |  3      |Process Level      210 |4      Department      85001
10245   |  3      |Process Level      210 |4      Department      20110
10262   |  3      |Process Level      230 |4      Department      23101
10262   |  3      |Process Level      230 |4      Department      23001

EMPLOYEE ELEMENTID ELEMENTNAME  |      X?  |Y?|    ELEMENTVALUE1   | Z?            
10198   |  3      |Process Level|      100 |4 |    Department      |10092
10230   |  3      |Process Level|      100 |4 |    Department      |85001
10230   |  3      |Process Level|      210 |4 |    Department      |85001
10245   |  3      |Process Level|      210 |4 |    Department      |20110
10262   |  3      |Process Level|      230 |4 |    Department      |23101
10262   |  3      |Process Level|      230 |4 |    Department      |23001

Open in new window

as we do not know your data would it be possible to see some with a separator between?

does this help?
SELECT
  R.RULENAME AS EMPLOYEE
, T.ELEMENTID
, replace( E.ELEMENTNAME ,'Process Level','') as X
, replace( V.ELEMENTVALUE1, 'Department','') as Z
...
0
 

Author Comment

by:garyjgs
ID: 39262631
REPLACE worked okay for text value,  but didn't for the data values.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39262673
thanks for that, but ...
would it be possible to see some data with a separator between?
e.g. supply some data in a comma separated file?

>>REPLACE worked okay for text value
yes, it is a text function only designed for text

>> but didn't for the data values.
what are the "data values" you tried it on?

all I can see is what you see above,
there are many spaces and columns of data - but no clear separation of the fields :(
0
 

Author Comment

by:garyjgs
ID: 39262766
The columns with the X? and Z? are data columns.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 200 total points
ID: 39262815
and they therefore need no 'replace' to be applied to them. (which would not work anyway)

is Y? also a 'data column', and if it is, then, all columns are already separate.

& what then remains to be done from the question?
The columns with Process Level/Department and values of 100/10092/100/85001 etc. reside in fields, ELEMENTNAME and ELEMENTVALUE respectively.  With SQL how can I create a separate column for ELEMENTNAME as well as create a separate column for the ELEMENTVALUE?
0
 

Author Closing Comment

by:garyjgs
ID: 39262858
I am going to use the query as is and create a Crystal Report, use formulas to put columns for Process Level and Departments, and Group by Employee.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

710 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