is it possible to turn 1 column,many rows into one row,many columns in sql ?

 Hi All

I have the following query:

Select CategoryName From Category Where CategoryId In(Select SkillId From SpecialitySkills Where CopywriterId = 13)

It gives me the correct results but not in the form that i want, i would like it to be in one row, many columns. its in 1 column, many rows.

Any ideas?

9/10/2008 3:38:40 PM 29906 articles. 0 followers. Follow

7 Replies

Similar Articles

[PageSpeed] 1

 You must be talking about displaying it on a web page? Just create a loop that runs through the rows of the data that is returned then just make html table:

dim tablestring as string = "<table><tr>"

for i as integer = 0 to dt.rows.count - 1

 tablestring += "<td>" & dt.rows(i).item(0) & "</td>"


tablestring += "</tr></table>"

That will switch it around for you.

Conrad Rosenbrock

Webmaster of Southern Inspirations Garden Design
9/10/2008 5:19:00 PM

Yes, google on "crosstab query SQL Server 2005". One of the first links (looks very helpful)

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
9/10/2008 5:27:54 PM

 Hi, has anyone done cross tab on a query before? Im so stuck and confused. would it be possible for some help if i post my actual query that returns the results in the many rows?


9/10/2008 6:26:19 PM

Yes, you can post your current query. I haven't played with cross-tab myself in SQL Server 2005 (only read in couple of books and tried few samples), but there were today threads about PIVOT and I guess we would be able to help you out (if not me, someone else).

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
9/10/2008 7:03:32 PM

 I have the following query@

Select SpecialitySkills.CopywriterId,Category.CategoryName,Customer.Username From SpecialitySkills,Category,Customer
Where SpecialitySkills.SkillId = Category.CategoryId And Customer.CustomerId = SpecialitySkills.CopywriterId And SpecialitySkills.CopywriterId In (Select SpecialitySkills.CopywriterId From SpecialitySkills Where SpecialitySkills.SkillId = 32)

It produces the following results:

CopywriterId CategoryName                   Username
------------ ------------------------------ --------------------------------------------------
13           Category1                User10
13           Category2                User10
13           Category3                  User10
13           Category4                    User10


I would like it to show it as

13 User10 Category1 Category2 Category3 Category4

Any ideas?

9/10/2008 7:21:42 PM
see this example. here i created a sample table and inserted some sample data. then i am getting your type of output. observe that and apply it on your query,


create table testpivot
  copywriterid int,
	categoryname varchar(100),
	username varchar(100)
insert into testpivot values (13,'cat1','user10')
insert into testpivot values (13,'cat2','user10')
insert into testpivot values (13,'cat3','user10')
insert into testpivot values (13,'cat4','user10')

declare @str varchar(1000)
set @str = stuff( replace(  ( select ','+categoryname from testpivot where copywriterid = 13 for xml path ('') ) ,',','],[') + ']',1,2,'')
declare @sql nvarchar(1000)
set @sql = 'select copywriterid,username,' + @str + ' from
(select * from testpivot) p
pivot (max(categoryname) for categoryname in (' + @str + '))pvt'
exec sp_executesql @sql
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
9/11/2008 5:25:02 AM

Hi, I managed to solve my problem. Check this link out, its a whole article dedicated to concatenating rows, a very good article:

9/11/2008 11:07:42 AM

Similar Artilces:

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 ...

How to divide 1 GridView Row with 1 column in 2 Rows with 5 columns?
Hi; I have a GridView with 10 rows and 1 column. How can I do to this GridView show the same data, but with 2 rows and 5 columns? In classic ASP, I made it this way: While (NOT rsFotos.EOF) if i mod 5 = 0 then Response.Write "</tr><tr>" end if ... Someone can help? :)  You have to use a custom template and lay the table out the way you want, either dropping controls in the template or creating a custom ITemplate that takes care of it for you. L...

How to transform SQL output rows into GridView rows+columns?
This is somewhat tricky to explain. What I am trying to do is link the output from a SQL query that is grouped into rows and turn that into a set of rows and columns. ex: turn this (from SQL): sqlcol1sqlcol2sqlcol3 row_1 col_a 1 row_1 col_b 2 row_1 col_c 3 row_2 col_a 4 row_2 col_b 5  into this (in GridView):  col_acol_bcol_c row_1 1 2 3 row_2 4 5     I am thinking that the best way of doing this is to inherit from GridView and to write a custom metho...

How to go through a datatable row by row and column by column
Hello, I have a dataset with a datatable and I need to insert each row from the datatable on my sql table, anyway there may be errors, so some rows may not be inserted because primary key constraints,  So I need to show the rows that are not inserted in a gridview, the hard thing to do is to go through all the datatable,  is there an wasy way to do it?MCPD ENTERPRISE APPLICATION DEVELOPER Start by looking here an update of your dataset, and if a row fails, you can mark it as invalid.You can ...

SQL to copy row and change 1 column value
How do I write the sql code to copy a row within a table, but at the same time change the value of one of the columns of the row being copied?  I am using SQL Server 2005, C#, stored procedures and a DAL to create my web site.   I am trying to write a stored procedure to copy all rows within a table of a user where the LanguageVersionID is say 17, and replace the LanguageVersionID with a new value of say 25, but keeping all other values the same. The two values (ExistingLanguageID & NewLanguageID) are derived from two drop down lists located on ...

How to handle 1 to many column insert in Sql server
Hi,    I have a table Called Actcodes and has 2 columns Name and Description...  And i want to insert the data from this table called PlanDBF to ActCode.. and this my insert statement...  INSERT INTO Statements..AscActCodes ( Name, Description ) SELECT ACT_CODE1, ACT_DESC1 ACT_CODE2, ACT_DESC2, ACT_CODE3, ACT_DESC3, ACT_CODE4, ACT_DESC4, ACT_CODE5, ACT_DESC5, ACT_CODE6, ACT_DESC6, ACT_CODE7, ACT_DESC7, ACT_CODE8, ACT_DESC8, ACT_CODE9, ACT_DESC9, ACT_CODE10, ACT_DESC10, ACT_CODE11, ...

create a one row data form with many table columns
I have a form with many table columns (let's say 10 columns). I want to the form display 5 column on top , 5 columns below . The form only needs to display one row at a time because it just retrieve one row at a time. Which control I need to use and how to do it? Thanks. I can not really understand what kind of GUI you have in mind. In case, your form retrieves one row each time, 10 labels or textboxes could be good enough. Am I missing something? Leon LangleybenMCSD, ASP.NET MVPBlog Yeah, you are right. I will create 10 textboxes then. Any samples? I know how to create a datag...

Need help formatting a GridView with too many columns to display in one row.
I have a gridview control that is bound to an OBjectDataSource that is returning 18 columns. The powers that be want to display 14 of these colums on a webpage. The data that is bound to the GridView can be logically categorized into two sections: Report Data and Custoner Data. Because displaying all 14 columns renders the resulting webpage nearly unreadable and unusable, I had originally designed the Gridview along with an acompanying DetailsView so that only 9 of the columns are displayed by default.   The new requirement is that I need to display every row's Details ...

How many rows is too many rows with a datastore.ImportFile() call?
How many rows is too many rows with a datastore.ImportFile() call? Thank you, Tom That might be like asking how many fries are too many. It depends on how big the fries are and how big your stomach is. The only constraint I've heard of (and I've loaded hundreds of thousands of rows into DataStores) is memory, and memory useage is a function of your row size. A row with a single integer column will take up a lot less memory than a row with 20 columns of 5K strings. In addition, you can get around the memory constraint by setting the RowsToDisk option on your DataWi...

SQL Reporting
Hello,  I need to make a report, but Im having a little problem formating the data into the specified format.  I have an Employee Benefits table and the benefits it returns needs to be as a column and either a yes or no as the row data. For example,    select eb.record_id, b.benefit_id, b.benefit, e.first_name, e.last_name from employee_benefits eb (nolock) left outer join employee e (nolock) on e.employee_id = eb.employee_id left outer join benefits b (nolock) on b.benefit_id = eb.record_id   This will return all the benefits for all the emplo...

computing the difference of a column in the current row from the column of the preceding row
Hello, a user asked me if we can solve following problem: we want to compute the difference (delta) of a column in the current row from the column of the preceding row. My first solution signs where: create a computed field (small_function_result) that takes the value from the preceding row: small(value, value, cf_getrow for all); where cf_getrow is a computed field like this: if(getrow()=1,getrow(),getrow()-1) create a computed field (Diff) that computes the difference: value-small_function_result My results should look like this: Row Value cf_getrow small_func...

GridView.Rows(Column=ID_PK) to GridView.Rows(Column=DropDownListTeamplteField)
Hi Guys,I wonder how I can assign the PrimaryKey in each GridView Row to the template field in the same row. whereas the template field is filled with a dropdownlist thatshould show the values for retrieved in a different table through the GridView Row primary key (which serves as the SELECT parameter to get these values) The GridView looks like this:<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="C_ID" DataSourceID="GetC_IDs"> <Columns> <asp:BoundField D...

One to many to many to many...
Hi all, I have an issue which i come up against on a regular basis and never solve it the same way twice... The issue is related to "one to many" table relationships and extracting data from multiple tables (more than 2)  As an example, say i had the following tables: WebsitesPagesFormsControls where a website has many pages, each page has many forms, and each form has many controls. Assuming I have a business object library with a class for each table, and a property in each representing the collection (and therefore the one to many relationship). My question is simply...

Move data from one column to another column and row
I need some help converting a table which has one row for each year.  Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns. I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows): Basic Information needed for Life Insurance 25 2005 Basic Information needed for Life Insurance 45 2006 Ea Chairmans Conference 10 2005 EA Chairmans Conference Press Release 33 2005 EA Chairmans Conferenc...

Web resources about - is it possible to turn 1 column,many rows into one row,many columns in sql ? -

NFL: St. Louis Rams Defeat Tampa Bay Buccaneers 31-23 As Fans Chant “Keep The Rams”
During last night’s NFL Thursday Night Football , the Tamp Bay Buccaneers traveled to St. Louis for what was potentially the last time the Rams ...

Former NBA Player Shawne Williams Arrested On Many Charges After Hit-and-Run
Shawne Williams, 17th overall pick in the 2006 NBA Draft, was arrested last night on a multitude of charges stemming from a car accident in Memphis. ...

Trump Flashback: Hillary 'A Great Appointment,' Obama EPA Appointment 'Brilliant Person, A Great Environmental ...
In 2008, Donald Trump, the current Republican presidential frontrunner, said, "Hillary is a great appointment," and called EPA administrator ...

Windows 10 mobile update delayed to beginning of 2016
Despite forecasting a rollout of the Windows 10 smartphone update in December several months ago, Microsoft now says the upgrade will take place ...

Rubio, Cruz and U.S. Global Leadership
At some point between 20016 and 2008, the American people decided to turn their backs on the world. Between the seeming futility of the war in ...

Apple’s secret design studio and new ‘Apple store of the future’ to be featured on ’60 Minutes’
Of all the secretive enclaves within Apple , none is more hallowed than Apple's notoriously quarantined design studio. Run by Jony Ive, Apple's ...

DNC Suspends Bernie Sanders Campaign from Data Access After Breach into Clinton Records
Bernie Sanders’ campaign improperly gained access to Hillary Clinton’s confidential voter information while a software patch was being applied ...

Facebook Tests “Click For More” To Boost Video Views On Desktop
Facebook says it now gets around 8 billion video views on its platform each day, and the social network has made no secret of its plans to make ...

Lifelock admits it lied in its ads (again), agrees to $100M fine
Lifelock, the tragicomically awful identity-theft protection service, has settled the FTC's suit against it , agreeing to pay a $100M fine for ...

‘Vikings’ Season 4: History Channel Announces Premiere Date, Double The Episodes & A Split Season
Fans of History Channel’s Vikings finally gets a start date for season 4 for the series based on the viking sagas and legends. As well as the ...

Resources last updated: 12/18/2015 3:18:37 PM