• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

SQL rows

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
garyjgs
Asked:
garyjgs
  • 4
  • 3
  • 2
  • +1
5 Solutions
 
selva_konguCommented:
You can use a CROSS APPLY to unpivot the data:
0
 
selva_konguCommented:
0
 
awking00Commented:
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!

 
garyjgsAuthor Commented:
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
 
PortletPaulCommented:
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
 
garyjgsAuthor Commented:
REPLACE worked okay for text value,  but didn't for the data values.
0
 
PortletPaulCommented:
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
 
garyjgsAuthor Commented:
The columns with the X? and Z? are data columns.
0
 
PortletPaulCommented:
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
 
garyjgsAuthor Commented:
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

Independent Software Vendors: 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!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now