ms sql -- display multiple columns, one row

How can I change one of the below to
"display multiple columns, one rows"
so it looks the the attached
 
I just want to use a SQL statement, no storedProc/etc.
------------------------------------------------------------------------------
  Test #1 --> displays one column, multiple rows
     SELECT status + '_' + work_center as WC_Status
     FROM job_operation
     where job = '1573' --> I will change this to a parameter later
------------------------------------------------------------------------------
  Test #2 --> displays one column, one row
     SELECT work_center + ','
     FROM job_operation
     where
       job = 'G0878' --> I will change this to a parameter later
     FOR XML PATH('')
s003.jpg
finance_teacherAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PortletPaulConnect With a Mentor freelancerCommented:
your image shows repitition of work_center across the row, so that's why I wonder if you want options A or B in particular. I think Sharath's prior code provides option D by the way.

code for options A, B  :: http://sqlfiddle.com/#!3/3de4d/6

-- Option A
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results;

DECLARE @query  AS NVARCHAR(MAX)

SELECT Job, Work_Center,
       ROW_NUMBER() OVER (PARTITION BY Job ORDER BY getdate()) row_ref
INTO #Results
FROM Job_Operation_test

;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN Work_Center ELSE null END) AS ['
            + convert(varchar,number) + ']'
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(row_ref)
                                    FROM #Results
                                    )
           )


select @query = 

  'select Job, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from #Results group by Job'

exec(@query)
;

-- Option B
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results;

DECLARE @query  AS NVARCHAR(MAX)

SELECT Job, Work_Center,
       ROW_NUMBER() OVER (PARTITION BY Job ORDER BY getdate()) row_ref
INTO #Results
FROM Job_Operation_test
WHERE work_center is not null


;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN Work_Center ELSE null END) AS ['
            + convert(varchar,number) + ']'
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(row_ref)
                                    FROM #Results
                                    )
           )


select @query = 

  'select Job, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from #Results group by Job'

exec(@query)
;
-- option C not available
;

-- option D
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results;

DECLARE @query  AS NVARCHAR(MAX)

SELECT  DISTINCT Job, Work_Center, 1 row_ref
INTO #Results
FROM Job_Operation_test
WHERE work_center is not null

UPDATE #Results
set row_ref = (
                select row_ref
                from (
                      select job, work_center
                  , ROW_NUMBER() OVER (PARTITION BY Job ORDER BY work_center) row_ref
                      from #Results
                     ) r
                where #Results.Job = r.Job and #Results.Work_Center = r.Work_Center
              )

;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN Work_Center ELSE null END) AS ['
            + convert(varchar,number) + ']'
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(row_ref)
                                    FROM #Results
                                    )
           )


select @query = 

  'select Job, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from #Results group by Job'

--select @query -- uncomment to inspect what the variable contains
exec(@query)
;

Open in new window

a different option D is here also but it probably offers nothing that using the pivot command won't
0
 
SharathData EngineerCommented:
Do you just want to transpose rows data to columns? Can you post some sample from your query?

SELECT status + '_' + work_center as WC_Status
     FROM job_operation
     where job = '1573'
0
 
finance_teacherAuthor Commented:
Attached is sample data for the below.

I want the end result to look like the attached, displaying multiple columns, but JUST one row per job #

I am only passing one job # parameter at a time.

CREATE TABLE [dbo].[Job_Operation_test](
      [Job] [dbo].[Key10] NULL,
      [Work_Center] [dbo].[Key10] NULL
s004.jpg
data.txt
0
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.

 
finance_teacherAuthor Commented:
attached is the FULL script if needed
script.sql
0
 
SharathData EngineerCommented:
Can you try this?
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
	
SELECT DISTINCT Job, Work_Center,
       ROW_NUMBER() OVER (PARTITION BY Job ORDER BY Work_Center) rn
  INTO #Results
  FROM Job_Operation_test;

DECLARE @listStr VARCHAR(MAX)
 SELECT @listStr = COALESCE(@listStr+',' ,'') + '[' + convert(varchar,rn) + ']' 
   FROM (select distinct rn from #Results) t1
 

SELECT @listStr = 
'SELECT *
  FROM #Results
 PIVOT (MAX(Work_Center) FOR rn IN ('+@listStr+')) P'

EXEC (@listStr)

Open in new window


Tested with some sample data: http://sqlfiddle.com/#!3/3025c/17
0
 
SharathData EngineerCommented:
Fixed duplicate data issue
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
	
SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY Job ORDER BY Work_Center) rn
  INTO #Results
  FROM (SELECT DISTINCT Job, Work_Center FROM Job_Operation_test 
         WHERE Work_Center IS NOT NULL) t1;

DECLARE @listStr VARCHAR(MAX)
 SELECT @listStr = COALESCE(@listStr+',' ,'') + '[' + convert(varchar,rn) + ']' 
   FROM (select distinct rn from #Results) t1
 

SELECT @listStr = 
'SELECT *
  FROM #Results
 PIVOT (MAX(Work_Center) FOR rn IN ('+@listStr+')) P'

EXEC (@listStr)

Open in new window


Tested with some sample data: http://sqlfiddle.com/#!3/3025c/19
0
 
finance_teacherAuthor Commented:
Below Test #1, Test #2, Test #3, and Test #4 work.

I then run Test #4 and Test #5 at the same time in MS SQL 2005 Server Management Studio and get an Incorrect syntax near '('. error.

How can I get the statements to work inside MS SQL Reporting Services or reduce it to SQL that works with MS SQL 2005 Server Management Studio ?
-----------------------------------------------------------------------------------------------------
--Test #1
DROP TABLE #Results

--Test #2
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Job ORDER BY Work_Center) rn
  INTO #Results
  FROM (SELECT DISTINCT Job, Work_Center FROM Job_Operation_test
         WHERE Work_Center IS NOT NULL) t1;

--Test #3
select * from #Results

--Test #4
DECLARE @listStr VARCHAR(MAX)
 SELECT @listStr = COALESCE(@listStr+',' ,'') + '[' + convert(varchar,rn) + ']'
   FROM (select distinct rn from #Results) t1;
SELECT @listStr =
'SELECT *
  FROM #Results
 PIVOT (MAX(Work_Center) FOR rn IN ('+@listStr+')) P'; -- Incorrect syntax near '('.

--Test #5
EXEC (@listStr);
0
 
SharathConnect With a Mentor Data EngineerCommented:
I did not see any issue. Can you run these and post the SQL printed.

--Test #4
DECLARE @listStr VARCHAR(MAX)
 SELECT @listStr = COALESCE(@listStr+',' ,'') + '[' + convert(varchar,rn) + ']'
   FROM (select distinct rn from #Results) t1;
SELECT @listStr =
'SELECT *
  FROM #Results
 PIVOT (MAX(Work_Center) FOR rn IN ('+@listStr+')) P'; -- Incorrect syntax near '('.

--Test #5
SELECT (@listStr);
0
 
PortletPaulfreelancerCommented:
I have a question about the expected results which your images don't reveal. In the following there are 15 rows of data for job 1573. Under that are 4 output options
a. the full 15, in the sequence found (with nulls)
b. as for a but nulls removed
c. unique work centers, based on sequence found
d. unique work centers, in alpha order
which of these is the required output?
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'ENGINEER')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'INSPECTION')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'INSPECTION')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'WATERJET L')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'HAASMINI 1')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'DEBURR')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'INSPECTION')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', NULL)
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', NULL)
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'GEN LABOR')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'PAINT')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'INSPECTION')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'GEN LABOR')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'SOURCE')
INSERT [dbo].[Job_Operation_test] ([Job], [Work_Center]) VALUES (N'1573', N'GEN LABOR')

a
ENGINEER,INSPECTION,INSPECTION,WATERJET L,HAASMINI 1,DEBURR,INSPECTION,NULL,NULL,GEN LABOR,PAINT,INSPECTION,GEN LABOR,SOURCE,GEN LABOR
b
ENGINEER,INSPECTION,INSPECTION,WATERJET L,HAASMINI 1,DEBURR,INSPECTION,GEN LABOR,PAINT,INSPECTION,GEN LABOR,SOURCE,GEN LABOR
c
ENGINEER,INSPECTION,WATERJET L,HAASMINI 1,DEBURR,GEN LABOR,PAINT,SOURCE
d
DEBURR,ENGINEER,GEN LABOR,HAASMINI 1,INSPECTION,PAINT,SOURCE,WATERJET L

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.