Solved

SQL rows

Posted on 2013-06-19
10
161 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
  • 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Join Query 12 50
Very interesting Access query problem. 13 70
Oracle - Create Procedure with Paramater 16 56
My Query is not giving correct result. Please help 5 27
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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now