Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL rows

Posted on 2013-06-19
10
Medium Priority
?
167 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 800 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 800 total points
ID: 39260373
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 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 49

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 49

Accepted Solution

by:
PortletPaul earned 800 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

885 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