split single column into multiple column

 Hi all,

I have table with 2 columns as shown below...

id     products_list

 1    a,b,c

2     d,e,f

3     g,h,i

Now I need a query which will ouput the result as below...

id     products_list1    products_list2       products_list3

 1    a                      b                          c

 2    d                      e                          f

 3    g                      h                          i

 How to achieve this...

4/28/2009 9:44:49 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 16
Get it on Google Play
Get it on Apple App Store

You have to use the COncept of Pivot tables in mssql2005. 

Please mark as answer if it helped u!
4/28/2009 10:44:37 AM

You can use functions for splitting,but I will propose you this sql without function:   

substring(products_list,1,charindex(',',products_list)-1) as products_list1,
substring( substring(products_list,charindex(',',products_list)+1,len(products_list)),1,charindex(',', substring(products_list,charindex(',',products_list)+1,len(products_list)))-1)  as products_list2,
substring( substring(products_list,charindex(',',products_list)+1,len(products_list)),charindex(',', substring(products_list,charindex(',',products_list)+1,len(products_list)))+1,len( substring(products_list,charindex(',',products_list)+1,len(products_list))))  as products_list3



Stefan Uzunov
4/28/2009 11:09:06 AM


pls check out the following code and reply me if it works.


select id, 
substring(products_list, 1, charindex(',', products_list)-1) products_list1,
substring(products_list, (charindex(',', products_list) + 1), (len(products_list)-charindex(',', reverse(products_list))-charindex(',', products_list))) products_list2,
substring(products_list, (len(products_list)-charindex(',', reverse(products_list)) + 2), len(products_list)) products_list3
from product_table
Irtizaur Rahman

Please: Don't forget to click "Mark as Answer" on the post that helped you. That way future readers will know which post solved your issue.
4/28/2009 11:11:48 AM

 Hey irtizaur,

ur code really works...thanks for ur reply...

have a nice day...!!!

4/28/2009 11:47:53 AM

Similar Artilces:

Split One Column into multiple columns
Hi all, I have a requirement like this  , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)   Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469   How can i acheive this one with out data lose in Address Column. Thanks in advance.      G.JaganMohanRaoMumbaiIndia If t...

SQL,Make Multiple values for multiple columns into one column separated by comma
Hi, I'm getting the result as this Name       Class       Subject------------------------------------------Anju         10             MathsAnju         10             Physics How to write the query to get the result like this Name       Class       Subjects------------...

Create multiple datagrid columns from single column
I have a datagrid that generates a single column of data that is extremely long. I'd like to find a method to create multiple columns from the single column so the data is easier to read. I have tried the DIV method to create a scroll bar, but it's still a single column of data. The other goal is to make the data printable. If I can crop the column when it hits the bottom of the page, and then start it again on a new column it will be easier to read and printable. Is this possible? Should I be using a different control other than a datagrid? Thanks!------------------------------...

Query split column in 2 columns in SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1                Col2 BMW               FordMercedes         Audi So i can bound a table directly to that column!Is this p...

Displaying single column of data across multiple columns..
Hi all... Okies my problem.... I have a dataset that has a single column, with 100 rows. I wan't to display this in a table with 4 columns with 25 rows in each column.. Does anyone know of a simple solution? Thank's in advance. Jeremy Baka Saru Hi, you can try the DataList with RepeatColumns property set to 4.  Alvin ChooiMicrosoft ASP.NET™ Enthusiast v1.1 / v2.0, Malaysia Blog : http://alvinzc.blogspot.com...

Displaying a single Data Window Column in multiple columns
I have a datawindow that is retrieving a single column. Using Powerbuilder 5.04, is there anyway to display this data in multiple columns sorted (alphabeticlly) vertically (eliminates n-up). Any help would be appreciated. Thanks Yehuda Newspaper columns? Yehuda wrote in message <3ACB5511.E9262E3C@yahoo.com>... >I have a datawindow that is retrieving a single column. Using >Powerbuilder 5.04, is there anyway to display this data in multiple >columns sorted (alphabeticlly) vertically (eliminates n-up). Any help >would be appreciated. >Thanks >Yehuda &...

displaying multiple columns in a gridview populated from a single column
hi, i'm a newbie so my apologies in advance if i am not explaining my problem clearly... i'm happy to repost to help clarify anything. Problem: i am not able to sort and organize my record set the way i need to to popluate my gridview. i am looking for suggestion on either how to construct my sql statement, or manipulate my gridview. Environment: i am working in asp.net 2.0 and using a sql server db. Details: I have a single table called Loans. i need to retrieve three columns from that table: LoanOfficer, LoanState, and LoanKey. The LoanState column contains one of three v...

Display multiple columns in Gridview from One Column of SQL table
Hi I have a table in sql with 2 columns and I want to display that in multiple columns in Gridview. SQL Table No.            Type 1               A1 1               B2 2              A2 2               B2 3        &...

When is a column not a column?
I have several extremely similar datawindows that I have to maintain that go against the same table. Recently, I had to add a column to that table, and also add the column to ALL of these datawindows that go against this table. I added the column to the first datawindow, set up the edit control style, etc. I then did a copy/paste of the columns edit control to the remaining dw's (of course remembering to first add the column in SQL). Everything (update props, column specs, column props) seems to indicate that the pasted column is linked to the column I selected, HOWEVER, when I...

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

multi-column HG index vs. multiple single-column HG index
In IQ 12.4.3, HG index could be created upon multiple columns. My questions are 1) in terms of query performance, which one is better -- one multiple-column HG index or multiple single-column HG indexes? 2) And what about the load performance? 3) If one column is part of a multi-column HG index, do it make sense to create a seperate HG/LF index for it? Thanks in advance, Richard Mei Number one, I don't know (great start, eh?). Speaking from ignorance, though, I would bet the following are all true: (1) If your HG index columns correspond exactly to your group ...

Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k.
Hi! Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k. Can anybody tell me how to do it?? Thanx. ::Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k. :: ::Can anybody tell me how to do it?? make it a string field, plus handle string manipulation. THAT SAID: this is short to a crime on the database. I strongly urge you not to do it and get your data model repaired.Thomas TomiczekPowerNodes ApS(Microsoft MVP C#/.NET)---Building a Website? Try the PowerNodes CMS - http://www.powernodes.com/ Hi! Tha...

Display Multiple Columns into One Column
My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset. SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2 FROM Hardware INNER JOIN [User] ON Hardware.UserID = [User].User...

how to split 1 column into mutiple column
Hi all, I have 2 column with the following results Sch         AvgScoreMadJ       40Denova    30Denova    40Atlantic   40Atlantic   40Atlantic   30Atlantic   30Denova    20Denova    20MadJ       20MadJ       20MadJ       20 How do i split this result into the following format? Sch         ...

Using @column = [column] SQL Server 2000
Should the following select statement return ALL the records including the records where [district] is null?  Currently it is only returning non null records.  I tried it with a default value and without with the same results. SelectCommand="SELECT [ID], [last], [first], [district], [TA] FROM [regd] Where ([district] Like case when @district is null then [district] else @district end)" <SelectParameters ><asp:ControlParameter ControlID="Last_textbox" Name="district" defaultvalue="%" PropertyName="Text" Type="Str...

Sorting columns when displaying single column in DataList
Currently, I am using the DataList to display the data. My DataList code looks something like this:<datalist><itemTemplate><tr><td>Name:</td><td><% DataBinder.....%></td></tr><tr><td>Company:</td><td><% DataBinder.....%></td></tr></itemTemplate></datalist>  My question is: How can I have an easy way of sorting this data. I would like the users to be able to sort the data by Name or by Company. I understand I can add AllowSorting to DataList or DataGrid, but then I would have to di...

Insert multiple values(multiple columns) based on single dropdown list selection.
What I would like to accomplish:  I have a dropdown list that is an entityref(populated by values from PK in separate table). When user makes selection in dropdown list, then clicks “Insert”, I would like the selected value to be inserted into the applicable FK column, but also would like to have a value equal to the integer selected, multiplied by 50, inserted into another column of the same table. Example with user selecting “2” in the dropdown list, inserting into Table b:table a: Column: Numbers(PK) (1, 2, 4, 8) Table b: Processors(FK) Inserted: “2”Table b: Factor Inserted: ...

adding 2 columns in a dataset as a single column in datagrid
hi, i am having 2 columns in a table in a dataset. i want to add those two columns and bind the resultant total as a single column to the datagrid. is it possible. if yes, how o acheive this? please help me. thanks in advance. muppidi.~ Muppidi A lot of ways.  One is to use a computed column in the dataset, then just bind to that.  Another way is to use something like this in your page: <%= Eval("column1")+Eval("column2") %>...

counting multiple values from the same column and grouping by a another column
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way. TheTable Order# Customer Status STATUS has valid values of PROCESSED and INPROGRESS The query I'm trying to build is Count of Processed and INProgress orders for a given Customer. I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query? Select Customer, Count (*) As Status1 FROM TheTable Where (Sta...

Writing a column after column
Hi all, I need to consolidate columns of data available across different directories into a single excel csv file. Usually we write to a file row after row but for the current task I have, it would be convenient to write the file column after column. Is there a file writing mode for this? If not, I guess, I will have to write the matrix row by row and then take a transpose. Let me know what you guys think and if you could share some skeleton of code for something like this would be great. Thanks. -vijay vijay wrote: > I need to consolidate columns of data available acro...

Alter Multiple Columns in SQL
This does not work, incorrect syntax... Please help: ALTER TABLE dbo.classifieds_HotelAds ALTER COLUMN ( HasValetParking varchar(1) NULL, HasContinentalBreakfast varchar(1) NULL, HasInRoomMovies varchar(1) NULL, HasSauna varchar(1) NULL, HasWhirlpool varchar(1) NULL, HasVoiceMail varchar(1) NULL, Has24HourSecurity varchar(1) NULL, HasParkingGarage varchar(1) NULL, HasElectronicRoomKeys varchar(1) NULL, HasCoffeeTeaMaker varchar(1) NULL, HasSafe varchar(1) NULL, HasVideoCheckOut varchar(1) NULL, HasRestrictedAccess varchar(1) NULL, HasInteriorRoomEntrance varchar(1) NULL...

how to fetch column using column index in sql server.
Hi, I have several columns in my table, but I want to fetch data using column index instead of column name in select  command. is there any way to do this.Please remember to click “Mark as Answer” on the post that helps youJasim AkhtarNew Delhi ( INDIA ) Yes there is.It's called dynamic SQL and it's a complicated process.See http://www.sommarskog.se/dynamic_sql.html Please click "Mark as Answer" if my reply solved your problem.CheersRammohan...

sql computing a column based on other aggrgate function column
hi all geeks,here I have one problem with sql query.The problem is that I am calculating sum of Quantity as Qty and cost_per_mtr*CaurrencyValue as UnitPrice and then as an another column I want Total as  computed from Qty and UnitPrice as Qty*UnitPrice. Is it possible in sql if yes plz let me know how, if no then plz tell me the workaround of this problem, Thanks  Sample Query:select c.CustomerName, c.cid,                 (select sum(qty) as Qty from order where id=c.id) as Qty ,       &n...

validation columns against column
Hi  Do anybody know some articles or codeexample of how to validate columns against a input Column. B can be > A and B can be < C. If you in row ID write 55 for Column_B, it must be reject.example:ID        column_A  Column_B   Column_C1              3               10             152         &...

Web resources about - split single column into multiple column - asp.net.sql-datasource

Code division multiple access - Wikipedia, the free encyclopedia
Code division multiple access ( CDMA ) is a channel access method used by various radio communication technologies. It should not be confused ...

Multiple injuries in fatal bus crash
A bus rollover near Airlie Beach on Tuesday leaves a 19-year-old man dead and several other passengers in a critical condition. Nine News

Queensland news: Bus rollover in Cannonvale leaves multiple passengers in critical condition - The Courier-Mail ...
A TEENAGER is dead, one person has lost a limb and others have suffered head injuries after a bus rolled on Queensland’s Whitsunday Coast.

Multiple injuries in fatal bus crash
... on Tuesday leaves a 19-year-old man dead and several other passengers in a critical condition. Nine News [ The Age ] The Age - Video Multiple ...

LastPass survey finds 95% of Americans share passwords, 59% use same password for multiple sites
... and sharing Netflix passwords with family members, but a survey by LastPass showed that 59% also re-use the same passwords for multiple sites. ...

Multiple Account Support for Facebook Messenger; SMS Option Testing
Facebook Messenger jumped aboard the multiple-account-support bandwagon, and the social network is testing an SMS option for the Android version ...

How to add multiple Instagram accounts on your iPhone
Multiple accounts are available now in Instagram , at long last ( and officially , too). But the process of configuring more than one account ...

Multiple casualties reported after explosion rocks Ankara, Turkey - Business Insider Deutschland
The explosion, which occurred just after 6:30 p.m. local time, apparently targeted shuttles carrying military personnel in central Kizilay district. ...

According to multiple reports, the Orlando Magic have traded Tobias Harris to the Detroit Pistons in
According to multiple reports , the Orlando Magic have traded Tobias Harris to the Detroit Pistons in exchange for Brandon Jennings and Ersan ...

Facebook is testing SMS, multiple accounts in Messenger
Facebook has slowly beefed up its Messenger offering over the past year, with not only the ability to send payments and hail an Uber ride, but ...

Resources last updated: 2/19/2016 7:52:00 AM