Example to create one row of data from multiple rows and have multiple columns,

I have a table with rows of data and I need to create one row of data that has multiple columns,  

Table1
 
ord           color
1               ink1
1              ink2
1              ink3
1              ink4

How can I role the table to look like this:
ord               col1    col2     col3     col4
1                   ink1    ink2     ink3    ink4

I tried the stuff command and that will just create     1 row with 2 fields,   ord     and ink    
                                                                                                                                  1         ink1,ink2,ink3,ink4
Thought this would be easy, and I am not a SQL expert by any means.  
But I could sure use some help in the way of an example.
Thanks.
Terry AmbrosiusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
>>"Thought this would be easy"

It is reasonably easy if you have a fixed number of columns.
Seriously not easy in SQL if you are expecting a variable number of columns. (But not impossible.)

Here is an example "pivot", and it is possble to make this work with a varable number of columns
declare @Table1 table
    (ord int, color varchar(4))
;
    
INSERT INTO @Table1
    (ord, color)
VALUES
    (1, 'ink1'),
    (1, 'ink2'),
    (1, 'ink3'),
    (1, 'ink4')
;

SELECT
      ord,ink1,ink2,ink3,ink4
FROM (
      SELECT
            ord
          , color
          , 1 as x
      FROM @table1
      ) s
PIVOT (
      SUM(x)
      FOR color IN (ink1,ink2,ink3,ink4)
      ) p
 

Open in new window


Personally I like using a more traditional approach involving case expressions and group by This is best suited to a fixed number of columns:
SELECT
      ord
    , max(case when color = 'ink1' then color end) as col1
    , max(case when color = 'ink2' then color end) as col2
    , max(case when color = 'ink3' then color end) as col3
    , max(case when color = 'ink4' then color end) as col4
from Table1
group by ord

Open in new window

0
JesterTooCommented:
This dynamic query will adjust to however many values you have in the color column without hard-coding the values as column names.

declare @sql nvarchar(max);

select @sql='select Ord,'+char(13)+char(10)+
stuff((select ','+char(13)+char(10)+'nullif(max(case when pos = '+cast(pos as varchar(3))+' then Color else '+char(39)+char(39)+' end),'+char(39)+char(39)+
       ') as '+quotename('Color'+cast(pos as varchar(3)))
       from (select distinct row_number() over(partition by Ord Order by Color)
             from table1
             )a(pos)
       for xml path(''), type).value('.','varchar(max)'),1,3,'')+char(13)+char(10)+'from (select Ord, Color, row_number() over(partition by Ord Order by Color)'+char(13)+char(10)+
space(6)+'from table1'+char(13)+char(10)+space(6)+')a(Ord,Color,pos)'+char(13)+char(10)+'group by Ord;';

execute sp_executesql @sql;       

Open in new window

0
Terry AmbrosiusAuthor Commented:
Thanks for the examples:
--------------------------------------------
with the pivot table example,  I get message on 2008 DB. where my data is residing,   can run on 2012 ok.
Error during Execute
 37000(325)[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
37000(8180)[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (0.07 secs)
---------------------------------------------

Example with the case statement,  
items are variable in the table, I should of said that, my mistake.  Also, with in the table I have over about 300 ink values,  (Ink1, ink2, ........ Ink3).  If I understand that correctly it would need 300 case statements?

------

I am trying to get the example from Jestertoo to work, but I thought the stuff command will only create 1 col.  (Ink1,Ink2,Ink3)..
that is what I saw when I tried the stuff command before posting to the site.   I am still trying to work thru the example.

Thanks
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Terry AmbrosiusAuthor Commented:
Getting message on the example with the stuff command,   will be researching that.  
Error during Execute
 37000(1934)[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (0.08 secs).

Tested with this SQL, so I know the stuff command works, but it strings the ink_codes together  (ink1value,ink2value,.......)
SELECT SPEC_NO ,  
Colors=STUFF  
(  
     (  
       SELECT DISTINCT ', ' + CAST(INK_CODE AS VARCHAR(MAX))  
       FROM spec_prt_info t2  
       WHERE t2.SPEC_NO = t1.SPEC_NO  
       FOR XML PATH('')  
     ),1,1,''  
),
'xxxxxx' as newfield  
FROM spec_prt_info t1  
where spec_no = 74573
GROUP BY SPEC_NO
0
PortletPaulEE Topic AdvisorCommented:
Over 300 values = over 300 columns.

To be honest i think this is a bad idea no matter how you attempt it. You would probably better off with a comma separated string.

What are you going to do with this query output?
0
Terry AmbrosiusAuthor Commented:
It could be up to 8 columns for order.  Group by order.    Pic attached,
order         inkvalue, inkvalue2, inkvalue3......inkvalue8.

so my table will have 9 columns,   ord, ink1,ink2,ink3.......ink8.    These are column headings not values in the table.

Inkvalues may be 1 to 300,   any combination.  Order can have up to 8 inks.

, max(case when color = 'ink1' then color end) as col1
    , max(case when color = 'ink2' then color end) as col2
    , max(case when color = 'ink3' then color end) as col3
    , max(case when color = 'ink4' then color end) as col4
Example.bmp
0
JesterTooCommented:
I have over about 300 ink values... that would have been useful to know at the outset.  The script I posted works correctly with the limited sample of data you posted.

There is a physical and practical limit to how many columns a query can handle.  At ~300 you are well below the 1024 physical limit (if the resulting rowsize limit of 8KB isn't exceeded) but the practical limit is much less than that.

I've modified the query I posted to accommodate an option to limit the number of columns to be shown... in this example, I set it at 15.  Feel free to experiment with it.  

declare @sql nvarchar(max);
declare @maxcols smallint;  set @maxcols = 15;

select @sql='select Ord,'+char(13)+char(10)+
   stuff((select ','+char(13)+char(10)+'nullif(max(case when pos = '+cast(pos as varchar(3))+' then Color else '+char(39)+char(39)+' end),'+char(39)+char(39)+
       ') as '+quotename('Color' + case when pos<@maxcols then cast(pos as varchar(3)) else cast(@maxcols as varchar(3)) end)
       from (select distinct row_number() over(partition by Ord Order by Color)
             from table2
             )a(pos)
       for xml path(''), type).value('.','varchar(max)'),1,3,'')+char(13)+char(10)+'from (select Ord, Color, row_number() over(partition by Ord Order by Color)'+char(13)+char(10)+
             space(6)+'from table2'+char(13)+char(10)+space(6)+')a(Ord,Color,pos)'+char(13)+char(10)+'group by Ord;';

execute sp_executesql @sql;

Open in new window

0
Terry AmbrosiusAuthor Commented:
Hi JesterToo,  sorry I should of explained that in more detail.  
When I try to execute the code,     I get the error message below.
I added my table and table names,  and my syntax looks to good..

Spec_no    and  INK_CODE where the fields.
If you see anything can you let me know.  
Thanks for the help!!

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

-------------------------------------
declare @sql nvarchar(max);
declare @maxcols smallint;  
set @maxcols = 15;

select @sql='select SPEC_NO ,'+char(13)+char(10)+
   stuff((select ','+char(13)+char(10)+'nullif(max(case when pos = '+cast(pos as varchar(10))+' then INK_CODE else '+char(39)+char(39)+' end),'+char(39)+char(39)+
       ') as '+quotename('INK_CODE' + case when pos<@maxcols then cast(pos as varchar(10)) else cast(@maxcols as varchar(10)) end)
       from (select distinct row_number() over(partition by SPEC_NO Order by INK_CODE)
             from spec_prt_info
             )a(pos)
       for xml path(''), type).value('.','varchar(max)'),1,10,'')+char(13)+char(10)+'from (select , SPEC_NO , INK_CODE, row_number() over(partition by  SPEC_NO Order by INK_CODE)'+char(13)+char(10)+
             space(6)+'from spec_prt_info'+char(13)+char(10)+space(6)+')a(Spec_No,INK_CODE,pos)'+char(13)+char(10)+'group by SPEC_NO;';
execute sp_executesql @sql;
0
PortletPaulEE Topic AdvisorCommented:
change the last 2 lines to read

select @sql;
--execute sp_executesql @sql;

Now grab the generated sql and try to format it here:
http://www.sql-format.com/

Did it format?

If not and you need more assistance paste the generated sql into a code block
code-in-the-toolbar1.png
0
JesterTooCommented:
Here is a repaired version of your modification...

declare @sql nvarchar(max);
declare @maxcols smallint;
set @maxcols = 15;

select @sql='select Spec_No,'+char(13)+char(10)+
   stuff((select ','+char(13)+char(10)+'nullif(max(case when pos = '+cast(pos as varchar(10))+' then INK_CODE else '+char(39)+char(39)+' end),'+char(39)+char(39)+
       ') as '+quotename('INK_CODE' + case when pos<@maxcols then cast(pos as varchar(10)) else cast(@maxcols as varchar(10)) end)
       from (select distinct row_number() over(partition by Spec_No order by INK_CODE)
             from spec_prt_info
             )a(pos)
       for xml path(''), type).value('.','varchar(max)'),1,3,'')+char(13)+char(10)+'from (select Spec_No, INK_CODE, row_number() over(partition by Spec_No order by INK_CODE)'+char(13)+char(10)+
             space(6)+'from spec_prt_info'+char(13)+char(10)+space(6)+')a(Spec_No,INK_CODE,pos)'+char(13)+char(10)+'group by Spec_No;';

execute sp_executesql @sql;  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Terry AmbrosiusAuthor Commented:
Thanks to all who responded.
0
Terry AmbrosiusAuthor Commented:
thanks to all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
multiple

From novice to tech pro — start learning today.