Solved

SQL rows

Posted on 2013-06-19
10
160 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 31

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

'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 …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now