Help: SQL one row to many rows
-
Folks,
I could use some help, I have a script that returns x number of jobs that were run. For each user associated with the job, I need to go out to another table to pull in one or more values associated with the user, each value has its own line. If I do a join, I get 1 of the values . I can;' use the pivot function because I'm not aggregating the data. any suggestions would be GREATLY appreciated. I basically want to create a new column for each line item in destination table or concatenate them into 1 new column. Bottom line, for each row in the query, I need the user's associated DEFAULT_USER_ROLE which can be more than one.DECLARE @START_DATE AS DATE;
SET @START_DATE='11/8/2017';
SELECT
CAST(RUN.RUN_INSTANT AS DATE) as RUN_DATE
,REPORT_INFO_NAME as REPORT_NAME
,TOTAL_EXE_TIME / 60 as RUN_TIME_MINS
,RUN_USER_ID as RUN_USER_ID
,EMP_RUN.NAME as RUN_USER
,RUN_DEPT_ID as 'Dept ID'
,clarity_dep.DEPARTMENT_NAME as Dept
,POP.NAME AS PRIVATE_OR_PUBLIC
FROM RW_RPT_RUN_DATA RUN
left outer join REPORT_INFO HRX on RUN.SOURCE_REPORT_ID = HRX.REPORT_INFO_ID
left outer join CLARITY_EMP EMP_RUN on EMP_RUN.USER_ID=RUN.RUN_USER_ID
inner join ZC_RW_PRIV_OR_PUB POP on POP.PRIVATE_OR_PUBL_C=HRX.PRIVATE_OR_PUBLIC_C
inner join CLARITY_DEP on run.RUN_DEPT_ID=CLARITY_DEP.DEPARTMENT_ID
left outer join CLARITY_EMP_ROLE as empr on run.RUN_USER_ID=empr.USER_ID
WHERE
CAST(RUN.RUN_INSTANT AS DATE) > @START_DATE
AND (TOTAL_EXE_TIME / 60) >='120'
OTHER TABLE EXAMPLE
- aobf - Thursday, April 12, 2018 9:58 AMFolks,
I could use some help, I have a script that returns x number of jobs that were run. For each user associated with the job, I need to go out to another table to pull in one or more values associated with the user, each value has its own line. If I do a join, I get 1 of the values . I can;' use the pivot function because I'm not aggregating the data. any suggestions would be GREATLY appreciated. I basically want to create a new column for each line item in destination table or concatenate them into 1 new column. Bottom line, for each row in the query, I need the user's associated DEFAULT_USER_ROLE which can be more than one.DECLARE @START_DATE AS DATE;
SET @START_DATE='11/8/2017';
SELECT
CAST(RUN.RUN_INSTANT AS DATE) as RUN_DATE
,REPORT_INFO_NAME as REPORT_NAME
,TOTAL_EXE_TIME / 60 as RUN_TIME_MINS
,RUN_USER_ID as RUN_USER_ID
,EMP_RUN.NAME as RUN_USER
,RUN_DEPT_ID as 'Dept ID'
,clarity_dep.DEPARTMENT_NAME as Dept
,POP.NAME AS PRIVATE_OR_PUBLIC
FROM RW_RPT_RUN_DATA RUN
left outer join REPORT_INFO HRX on RUN.SOURCE_REPORT_ID = HRX.REPORT_INFO_ID
left outer join CLARITY_EMP EMP_RUN on EMP_RUN.USER_ID=RUN.RUN_USER_ID
inner join ZC_RW_PRIV_OR_PUB POP on POP.PRIVATE_OR_PUBL_C=HRX.PRIVATE_OR_PUBLIC_C
inner join CLARITY_DEP on run.RUN_DEPT_ID=CLARITY_DEP.DEPARTMENT_ID
left outer join CLARITY_EMP_ROLE as empr on run.RUN_USER_ID=empr.USER_ID
WHERE
CAST(RUN.RUN_INSTANT AS DATE) > @START_DATE
AND (TOTAL_EXE_TIME / 60) >='120'OTHER TABLE EXAMPLE
You could pivot the data using MAX(). The max value of one row is that same row. However, I'd recommend that you use a cross tabs query.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply