Converting Rows Into Columns
Hi,
I have written a query that I need to get data which are to be displayed in the SQL 2005 reports.
I am getting the result as
Data Count
A 2
B 4
C 5
I need to get the data in a single row and the data in the 'Data' column should become like a column, like
A B C
2 4 5
Can anyone help me with this query?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
View Replies !
View Related
Converting Rows To Columns
have a urgent requirement. Please somebody help me.I have a table departinfo with following recordsbegin_time end_time Name Pieces10:00 10:15 PopCorn 310:15 10:30 Biscuits 510:30 10:45 PopCorn 2Now I need to run a sql query and the output should be as below :begin_time end_time PopCorn Biscuits10:00 10:15 3 010:15 10:30 0 510:30 10:45 2 0Please note that only one column i.e. PopCorn is created in spite ofhaving multiple records in the table. Similarly the records are notfixed. I mean thatthere can be n number of records and the columns should be uniquelycreated.Can somebody help me outPLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
View Replies !
View Related
Converting Rows Into Columns MS SQL 2K
I have a SP that returns the information I want but it returns it in 2separate queries.Example:Query 1Name, Number, ClassRow 1- Mike Phillips, 154AA, AAandQuery 2Time, ManualRow 1 -12:45:22,0Row 2 -13:04:56,0What I want it to look like is:Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0Here is the query I'm using:DECLARE Class cursorFOR--here we get a list of distinct classes to pass to the Class cursorselect Distinct(class_ID) from kt_member_lapwhere Race_ID = 83order by Class_ID;OPEN Class;DECLARE @RaceID intDECLARE@RacerCount intDECLARE @ClassID char(50)DECLARE @classcountDECLARE @Racer char(50)DECLARE @i intSET @RaceID = 83--this is where we loop through the classesFETCH NEXT FROM Class INTO @ClassIDWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)DECLARE Lap cursorFORSelect DISTINCT(Member_ID) from KT_MEMBER_LAPWhere class_ID = @classID and race_id = @RaceIDOPEN Lap;--this is to begin counting from the first lapSET @i = 1;FETCH NEXT FROM Lap INTO @RacerWHILE (@@FETCH_STATUS <> -1)BEGINIF (@@FETCH_STATUS <> -2)SELECT KT_MEMBER.MEMBER_FNAME + ' ' +KT_MEMBER.MEMBER_LNAME As MemberName,CONVERT(nvarchar(3),KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER AsBikeNumber,KT_CLASS.CLASS_DESCFROM KT_CLASS INNER JOINKT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =KT_MEMBER_CLASS.CLASS_ID INNER JOINKT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =KT_MEMBER.MEMBER_IDWHERE KT_MEMBER.MEMBER_ID = @Racer and KT_CLASS.CLASS_ID =@ClassID--SELECT @Racer, @ClassIDSelect MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAPWhere Member_ID = @Racer and class_ID = @classID and race_id =@RaceIDORDER BY MEMBER_LAP_TIME_REAL--here I count up for the next lapSET @i = @i + 1;FETCH NEXT FROM Lap INTO @RacerENDCLOSE Lap;DEALLOCATE Lap;FETCH NEXT FROM Class INTO @ClassIDENDCLOSE Class;DEALLOCATE Class;Any help would be appreciated.
View Replies !
View Related
Converting Rows Into Columns
hi, i have the 4rows in one table those are book names... book1 book2 book3 book4 i have the other table..consisting of usenames in the output i need like this username1 book1 book2 book3 book4 username2 book1 book2 book3 book4
View Replies !
View Related
Converting Rows To Columns In SQL7
I do a:SELECT * FROM xxxAnd Get:Date Place SumA M 1A O 3 A P 2B O 5B M 4B P 2And I want it to look like:Date M O PA 1 3 2B 4 5 2Can you think of an EASY way to do this?I can do it with a cursor that constructs a SQL statement, which I EXEC, but the 8000 character limit may prove to be a limiting factor.sp_execsql is somewhat messy for the nature of this issue.Any input is appreciated.Thanks in advance.
View Replies !
View Related
Converting Rows To Columns Using Case.Problem With
SELECT h1.ftraccode, CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec', CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec', case when ftraaddred ='A' then h1.ftradesc end as 'From Description', case when ftraaddred ='r' then h1.ftradesc end as 'to Description' from bHISfile h1 where h1.ftradesc like 'sw%' order by 1 ---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |NULL | MMTEI |NULL |SWITCH TO OAPIF ABADJ16421 |OAPIF | NULL |SWITCH FROM MMTEI |NULL 2(row) Expected output like this ---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |OAPIF | MMTEI |SWITCH FROM MMTEI |SWITCH TO OAPIF 1(row)
View Replies !
View Related
Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View Replies !
View Related
PIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns
Hello All, I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem. Consider that we have Product Category, Product and its monthly sales information retrieved as follows: CategoryID ProductID ProductName Month UnitPrice QtySold SalesAmount 1 1 Panel Jan 5 10 50 1 1 Panel Feb 5 15 75 1 1 Panel Mar 5 20 100 1 2 Frame Jan 10 30 300 1 2 Frame Feb 10 25 250 1 2 Frame Mar 10 20 200 1 3 Glass Jan 20 10 200 1 3 Glass Feb 20 20 400 1 3 Glass Mar 20 30 600 I would like it to be converted into following result set: CategoryID ProductID ProductName UnitPrice QtySold_Jan SalesAmt_Jan QtySold_Feb SalesAmt_Feb QtySold_Mar SalesAmt_Mar 1 1 Panel 5 10 50 15 75 20 100 1 2 Frame 10 30 300 25 250 20 200 1 3 Glass 20 10 200 20 400 30 600 I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses. Thanks.
View Replies !
View Related
Turn Columns Into Rows And Rows Into Columns
I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data. Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount 123, Grenadine, 100.00, 75.00, 15.50, 2.00 456, Lime Juice, 120.00, 81.00, 17.25, 2.00 There could be 1 item or 4000 items. What I want to see is. Item # - 123, 456 Description - Grenadine, Lime Juice CaseSalePrice - 100.00, 120.00 Cost - 75.00, 81.00 BottleSalePrice - 15.50, 17.25 Discount - 2.00, 2.00 What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see. I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect. The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing. I am all out of creative ideas, any help would be appreciated.
View Replies !
View Related
Converting Nrows Into Columns
Hi, I need to flip around the way data is presented in a table. I've got columns called 'datafield', and 'datavalue' in one table and I want to move the datavalue into a second table where the column name is the same as the datafield. Below is the sql to create the first table and populate it, I've also included the SQL to create the second table. I want to move the data from teststart to testfinish. There may also be columns in test finish that aren't included in the data of teststart. create TABLE teststart ( ticker varchar(255) NOT NULL, datafield varchar(255) NOT NULL, datavalue varchar(255) NOT NULL ) INSERT INTO teststart select 'ZTX LN Equity', 'EXCH_CODE', 'LN' union select 'ZTX LN Equity', 'ID_BB_COMPANY', '112367' union select 'ZTX LN Equity', 'ID_BB_SECURITY', '1000' union select 'ZTX LN Equity', 'ID_ISIN', 'GB0008812496' union select 'ZTX LN Equity', 'ID_MIC_LOCAL_EXCH', 'XLON' union select 'ZTX LN Equity', 'ID_SEDOL1', '0881249' CREATE TABLE testfinish ( ticker varchar(255) NOT NULL, EXCH_CODE varchar(255), ID_BB_COMPANY varchar(255), ID_BB_SECURITY varchar(255), ID_ISIN varchar(255), ID_MIC_LOCAL_EXCH varchar(255), ID_SEDOL1 varchar(255), Another varchar(255) ) I'd be grateful for any pointers, thanks. Sean
View Replies !
View Related
Problem Converting Rows To A String
I try to accomplish the following:I have two tables which are connected via a third table (N:Nrelationship):Table 1 "Locations"LocationID (Primary Key)Table 2 "Specialists"SpecialistID (Primary Key)Name (varchar)Table 3 "SpecialistLocations"SpecialistID (Foreign Key)LocationID (Foreign Key)(both together are the primary key for this table)Issuing the following commandSELECTL.LocationID , S.[Name]FROMLocations AS LLEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationIDLEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistIDresults in the following table:LocationID | Name1Specialist 11Specialist 22Specialist 32Specialist 43Specialist 14Specialist 4Now my problem: I would like to have the following output:LocationID | Names1Specialist 1, Specialist 22Specialist 3, Specialist 43Specialist 14Specialist 4....which is grouping by LocationID and concatenating the specialistnames.Any idea on how to do this?Thank you very much,Dennis
View Replies !
View Related
Converting Rows Records Into Table Fields
Hi I have a table that contains duplicates. Custid, order #s custid,order# 1, 2525 1, 2323 1, 2222 2, 6969 2, 7474 3, 8888 Here what I am trying to do. I want to create a table that contains rows from the above table in this format. custid, order#,order#,order# 1 ,2525 ,2323, 2222 basically, I want to convert the duplicate rows into fields in a new table.
View Replies !
View Related
Converting Repeating Fields In Single Row To Multiple Rows
Hi there I have loaded a csv file into a table. Some fields within the file contain a varying number (up to 10) of subfields seperated by line feed characters. It looks sort of like this Customer No. Payments Dates 111 pay1|pay2|pay3 dat1|dat2|dat3 I created an Unpivot transformation, and I got Customer No. Description Detail 111 Payments pay1|pay2|pay3 111 Dates dat1|dat2|dat3 After a Derived Column transformation I got Customer No. Description Line1 Line2 Line3 111 Payments pay1 pay2 pay3 111 Dates dat1 dat2 dat3 But what I really want is to end up with this: Customer No. Payments Dates 111 pay1 dat1 111 pay2 dat2 111 pay3 dat3 Is there a transformation that will get me there, or do I just need some cunning SQL? I tried to Pivot my way back to happiness but I couldn't get it to work
View Replies !
View Related
Columns Vs. Rows
hi,I'm building a multi-lingual website In my database tables I have, in some of them, a column with the Language, because some of the columns depend on what language the user wants to see the site.My question is: what is better? have that column and consequently two row (for two languages) with repeated column information? or have two column within a row with the language specification?e.g. table: id, description, price(1) With language:id,description, price, language='EX' id,description, price, language='EN' vs.(2) id, descriptionEN,descriptionEX,price if I have 500 products in 1 whould result in 1000 entriesin 2 just 500 results can anyone tell me a diference/advantage between the two approachs?thanks in advance.
View Replies !
View Related
Rows As Columns
I know this is a self join, but I can't remember exactly how it goes.Could someone help me out?create table A{int idA,varchar(30) dataA}create table B{int idB,varchar(30) dataB}create table A_B{int idA references A(idA),int idB references B(idB)}insert into A values(1, "foobar")insert into A values(2, "barfoo")insert into B values(1, "a")insert into B values(2, "b")insert into B values(3, "c")insert into B values(4, "d")insert into B values(5, "e")insert into B values(6, "f")insert into B values(7, "g")insert into B values(8, "h")insert into A_B values (1, 1)insert into A_B values (1, 2)insert into A_B values (1, 3)insert into A_B values (1, 4)desired resultsfoobar a b c dThanks,-- Rick
View Replies !
View Related
Rows Become Columns Help!
I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to make it as the format in table B as final. How to convert it? Please help! Thank you! Suin
View Replies !
View Related
Rows As Columns
Hi i have rows as follows 1 col1 value1 1 col2 value2 1 col3 value3 2 col1 value4 2 col2 value5 2 col3 value6 need result as id col1 col2 col3 1 value1 value2 value3 2 value4 value5 value6 How to achieve this? I am trying to achieve this using Pivot, but it is aggregating.
View Replies !
View Related
XML Columns To Rows
I have a large amount of data in an XML file. The file is very simple. Off of the root node are category nodes and each category node has many detail nodes. The detail node consist of the node name and the node value. Rather than map the detail to matching named fields in a single record, I would like to send the detail node data to individual data records where the category code, node name and node value are the only fields in the record. For example, Starting with <Category> <Code>001</Code <Details> <DetailCode123>75.87</DetailCode123> <DetailCode728>12.98</DetailCode728> <DetailCode387>55.72</DetailCode387> </Details> </Category> I want to be able to pick up the 001 code for the category and then transform the above data into 3 separate table records with the following fields (CatCode, DetailCodeName, DetailValue). 001, DetailCode123, 75.84 001, DetailCode728, 12.98 001, DetailCode387, 5572 Does this sort of transform exist? If not, I will have generate my own parsing script. Thought it was worth asking before doing the additional work. Thanks
View Replies !
View Related
Columns To Rows
Lets say I have the following rows.. ID,Net,Gross,Total 1 ,25.00,55.00,100.00 2,35.00,65.00,250.00 What would be the best way to do this... ID, Description, Value 1, Net, 25.00 1, Gross,55.00 1, Total, 100,00 2, Net 35,00 I was using the multicast and doing a bunch of derived columns but it seems like there should be a eaiser way to do this. Any help would be appreciated, thanks. Mardo
View Replies !
View Related
Rows Into Columns
I am converting rows into columns : Schedule_S1 contains more than 1 rows with different FROM_DATE and TO_DATE The query should merge 2 rows into 1 row with 2 different FROM_DATE AND TO_DATE The following query works fine when there are more than 1 records. When there is only 1 record, it should still return the record (with FROM_DATE_2 and TO_DATE_2 as nulls). Thanks for any help. SELECT ml1.P_ID, ml1.S_ID, ml1.CURRENT_DAY, ml1.FROM_DATE AS "FROM_DATE_1", ml1.TO_DATE AS "TO_DATE_1", ml2.FROM_DATE AS "FROM_DATE_2", ml2.TO_DATE AS "TO_DATE_2" FROM Schedule_S1 ml1 JOIN Schedule_S1 ml2 ON ml2.FROM_DATE > ml1.FROM_DATE WHERE ml1.P_ID = 59014 AND ml1.S_ID = 25691 AND ml1.CURRENT_DAY = 3 AND ml2.P_ID = 59014 AND ml2.S_ID = 25691 AND ml2.CURRENT_DAY = 3 ORDER BY ml1.FROM_DATE
View Replies !
View Related
Rows And Columns
I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?
View Replies !
View Related
Rows To Columns
Afternoon I am trying to write a query that will return the columns: year/ month, each status type (unknown how many types there are) Each row is a different join year/ month Each cell has the count of users that joined in that rows year/ month and currently have the status of the column. At the moment I have the following query: SELECT [remortgage-status].status, COUNT(1) AS CountTotal, YEAR([remortgage-log].datetime) AS Year, MONTH([remortgage-log].datetime) AS month FROM [remortgage-status] INNER JOIN [remortgage-log] ON [remortgage-status].clientid = [remortgage-log].clientid WHERE ([remortgage-log].action = N'Joined') GROUP BY [remortgage-status].status, YEAR([remortgage-log].datetime), MONTH([remortgage-log].datetime) The problem is that each different status is a new row rather than each status being a column. What do I need to do to correct this? - I dont know all the different possible statuses at this point
View Replies !
View Related
Rows Become Columns
hello all, i am trying to create a view from a table that will keep track of the time between each stage of tasks given. take a look at the data below: progressID taskIDstage status theDate ------------------------------------------------------------------------ 1407525 1091657In Progress Logged 2006-11-16 10:00:24.000 1407526 1091657 In Progress Inprogress 2006-11-16 12:08:59.036 1407214 1091657In Progress Resolved 2006-11-16 14:15:48.000 1407220 1091657Closed Solved 2006-11-16 14:36:05.000 i would like to be able to have just one row per task ID showing the difference between the stages, as shown below . only the 2nd column is a date, the rest are are hours (datediff) between the stage and its preceeding stage : taskID Logged InProgress Resolved Solved 1091657 2006-11-16 10:00:24.000 2.08 2.07 0.21 is it possible to achieve such a transformation using views and a number of select statements (i.e no dts)? all assistance will be highly appreciated. regards, ptah
View Replies !
View Related
Columns Into Rows
Hi, I've a table called months with one column month. The result set will be month ===== Jan Feb Mar ... Now i want to convert those values as rows. Means, Jan Feb Mar =========== Suggest me a solution for the above problem. Thanks Somu
View Replies !
View Related
Mapping Columns To Rows
Hi I have 2 tables defined as follows: Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID Table2 = FormUID, Label, Position When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable e.g. lets say Table2 contains the following 1, customerName, 1 1, customerTitle, 2 1, customerDOB, 3 and Table1 might contain 1, Paul Jones, Mr, 21/09/1987, 1 when I query Table1 I would get uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987 what I would like to get is uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987 I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns. any help would be great regards
View Replies !
View Related
Convert Rows To Columns
Hi All,I need to help with converting rows to columns in SQL2k.Input:Id Name Role58Ron Doe Associate58Mark BonasDoctor59Mike JohnsonDoctor59John SmithAssociate102Chris CarterAssociate102Ron Doe Associate102James JonesAssociateOutput should look like:IdDoctorAssoc1Assoc2Assoc358Mark BonasRon Doe NULLNULL59Mike JohnsonJohn SmithNULLNULL102NULLChris CarterRon Doe James JonesThere could be more than 3 associates in the input but I only need 3above columns for associates.I used following query:SELECT Q.sales_id,doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'and Q2.sales_id = Q.sales_id),assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id),assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role ='associate' and Q2.sales_id = Q.sales_id)FROM view1 QGROUP BY sales_idand I get this error "Subquery returned more than 1 value" since thereare multiple associate for Id 102.Thenks
View Replies !
View Related
Transforming Rows Into Columns
Hi All,I'm not sure how best to describe what I'm really looking to do, soI'll just give you my example and hopefully someone can help.I have a table that looks similar to this:Primary Key Field Type Field Value--------------------------------------1234 FName Sally1234 LName Smith1234 Phone 555-555-55557777 FName John7777 LName Riley4444 LName Johnson4444 Phone 222-234-5555etc...Which I want to move into a format that looks like this:Primary Key FName LName Phone-----------------------------------------1234 Sally Smith 555-555-55557777 John Riley4444 Johnson 222-234-5555What is the most efficient way to do this? I'd rather not create aseparate query for each of the field types (there are more than 3 in myactual situation).Any help you can offer would be appreciated!Jeff
View Replies !
View Related
Transpose Rows To Columns ?
I have recordsID Sku Name Date2 41 Blair 01/04/033 45 John 03/04/03that should look like...ID 2 3Sku 41 45Name Blair John.....and so on. Number of source rows will be fixed (12) so no of targetcolumns will be 12 too.Anyone know of a quick way to do this via simple SQL ?
View Replies !
View Related
Multiple Rows And Columns MAX
Hi All,I'm using the sample table:ID | CODE | V1 | V2 | V3----------------------------------------------------------------1 3 10 3 431 4 9 8 221 2 6 2 551 5 57 12 6I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)and their respective CODEs.For the above table the returned record for ID=1 should be:v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2currently I've got a very messy solution, I'mlooking for an elegant way to do this.10x,Assaf.
View Replies !
View Related
Transposing Rows To Columns
I hope someone can help me with my problem. I have searched theinternet for days for a solution, but nothing i found seemed to work.The following is what i have now:appartmentid code100306 Wip100306 Zandbak100306 Glijbaan100306 Klimrek100306 Schommel100321 Glijbaan100321 SchommelThis results are made with this query:selectappartment.appartmentid,listvalue.codefrom appartmentinner join appartmentlistvalue on appartmentlistvalue.appartmentid =appartment.appartmentidinner join listvalue on appartmentlistvalue.listvalueid =listvalue.listvalueidThe following is what i need:100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel100312 Glijbaan, SchommelAs you can see is this example, not all appartments have the samenumber of results. Can anyone tell me if this is possible?Many thanks,Sakymoto
View Replies !
View Related
Turning Rows Into Columns
Say I have a table of data containing something likeRegion | County | Year | Month | Valuefor some sort of value (int). I want to re-arrange this data so that itcomes out like this:Region | County | Year | J | F | M | A | M | J | J | A | S | O | N | Dwhere the letters are obviously the months in order. How would I goabout this/what's the best way. I attempted to use 12 INNER JOINS onthe table itself, sadly that failed miserably. Also, this doesn't seemvery efficient?Before you ask I got rid of my original code (gave up!)
View Replies !
View Related
Moving Rows To Columns
I've seen this answered a number of times in the group, but now that Iactually need the answer I don't know where to look or what to put inthe search.The question is this:I have a query that produces these reults:Col1 Col2A BA CA DB AB CB DI would like take the above reults, manipulate them and get these:Col1 Col2 COl3 Col4A B C DB A C DRight now I've got a loop through a cursor, which is ok. It works,but I'm worried about it because if I end up with a large table in thefuture, the longer it will take to run the query with the curors.Can someone help me please - point me to previous answers to thisquestion?Thanks,Jennifer
View Replies !
View Related
Representing Rows As Columns
HiI have a table in SQL Server 2000 that has following data:PunchTime PunchType11:45:00 In12:45:00 Out1:45:00 In3:15:00 OutIs there a way in SQL to represent this in the following format:In Out In Out11:45:00 12:45:00 1:45:00 3:15:00Thanks
View Replies !
View Related
Transposing Rows Into Columns
Hello,is there any quite easy solution for the problem of transposing the rowsinto the columns? I have the following table with some data in it:dealer date 09.00 10.00 11.00 12.00 13.00 14.00-----------------------------------------------------------------1 2004-10-01 1/1 2/3 3/3 3/4 4/5 0/31 2004-10-02 0/1 1/3 1/3 1/4 3/5 1/3/and so on.../I'd like to prepare a SELECT query in the stored procedure that willdisplay this data in the following form:dealer date hour reservations------------------------------------------------------------------1 2004-10-01 09.00 1/11 2004-10-01 10.00 2/31 2004-10-01 11.00 3/31 2004-10-01 12.00 3/41 2004-10-01 13.00 4/51 2004-10-01 14.00 0/31 2004-10-02 09.00 0/11 2004-10-02 10.00 1/31 2004-10-02 11.00 1/31 2004-10-02 12.00 1/41 2004-10-02 13.00 3/51 2004-10-02 14.00 1/3Is it possible to do it using some simple solution? I saw some possiblesolutions but they are a bit confusing. Any ideas? Thanks in advance....:: fabio
View Replies !
View Related
Transpose Rows Into Columns
Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table ID type 1 A 1 B 2 A 2 C 2 D etc,.... I want to tranpose to some like ID type 1 A,B 2 A,C,D etc,... or ID type1 type2 type3 1 A B 2 A C D etc,.... Really appreciate any input.
View Replies !
View Related
Rearrange Rows And Columns
I have a table with three columns: AcctNbr, Type, CodeValue Listed below is an example of the database. Code: AcctNbr Type CodeValue 1 MAILCODE 99 2 MAILCODE 99 3 MAILCODE 99 4 MAILCODE 90 4 MAILCODE 99 4 SEG1 O 5 MAILCODE 99 6 MAILCODE 99 7 MAILCODE 99 8 MAILCODE 99 9 MAILCODE 99 10 MAILCODE 90 11 MAILCODE 99 12 MAILCODE 99 13 MAILCODE 99 14 MAILCODE 99 15 LIST DS1 15 MAILCODE 99 There are multiple Type's for some AcctNbr's and what I want to do is run a query on the database so that if the AcctNbr has multiple Type's and CodesValue's it takes them and creates new columns like so: Code: AcctNbr MailCode_90 MailCode_99 SEG1 4 90 99 O So on and so forth. There are multiple Type's and multiple codes that I need to do this with for each account number. If someone could give me a base code to try I could start somewhere. I am an SQL novice. Thanks. Josh
View Replies !
View Related
Convert Rows Into Columns
Hi, I have a table which consists of some rows i want to get rows as columns For eg: table1 id name dept 1 x a 2 y b 3 z c i want to write a select query to fetch result as 1 2 3 x y z a b c can anyone help please
View Replies !
View Related
Transport Rows To Columns
Hi, I have data like this: Appid | CID | Level | Col1 | Col2 | Col3 1 | 100 | man 1 | 101 | null 1 | 102 | null 2 | 100 | null 2 | 101 | xyz 2 | 102 | null 3 | 100 | null 3 | 101 | null 3 | 102 | abc I have only 3 values for the CID column. 100, 101, 102. I need to get result like this: AppID | L1 | L2 | L3 | Col1 | Col2 | Col4 1 man null null 2 null xyz null 3 null null abc How to get this result? The Level column values are not in alphabetical order. Here L1 column contain values of CID is 100. L2 column contain values of CID 101. L2 column contain values of CID 102 Thanks in advance
View Replies !
View Related
Turning Rows Into Columns
I have a denormalization question that seems fairly fundamental but I haven't found the answer in BOL. I have data stored in a normalized transaction oriented database that I would like to denormalize to do some queries/analysis. Many tables contain attributes that are virtual columns driven by configuration. I am struggling with how to take those rows of data and turn them into columns of data. Example Source: Column1: CustomerId Column2: AttributeType Column3: Attribute Value Ex Data: 123, ShoeSize, 9 123, Age, 45 123, Gender, Male I would like to turn that into a table with one row, many columns: CustomerId, ShoeSize, Age, Gender 123, 9, 45, Male Also, I have other tables that are keyed off of the CustomerId that I would like to append to my ouput table via more columns. For example, a customer's address. Example Source: Column1: CustomerId Column2: AddressLine1 Column3: AddressLine2 Column4: City Column5: State Column6: Zip If I need to combine several tables, should I nest several merge transformations? Thanks, Craig
View Replies !
View Related
How To Convert Rows To Columns
hi i have a sample data like patientno visit experimentdate 101 1 23-Dec-2004 101 2 23-Mar-2005 101 3 23-Jul-2005 102 1 23-Dec-2004 102 2 23-Mar-2005 102 3 23-Jul-2005 i want it to display like this patientno visit1 visit1date visit2 visit2date visit3 visit3date 101 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005 102 1 23-dec-2004 2 23-Mar-2005 3 23-Jul-2005 ..please suggest how can we do it
View Replies !
View Related
Saving Columns As Rows :)
Hello to everyone, I've a simple association rule experiment. I have one table: [STUDENT]. In this table there are lots of attributes. Likewise 30 of the columns contains a questionnaire that has some possible values : 1 (Strongly agree), 2(agree)... 6 (Disagree). I made a new table: [STUDENTPOLL] and i've coded a c# program which reads every student and insert the aggreed values to the new StudentPoll table as a new row. ---------------------------------------------------- ---------------------------------------------------------- | STUDENT ID | STUDENT NAME | | ID | STUDENT ID | Agreed Question | |---------------------------------------------------| |---------------------------------------------------------| | 1 | George | | 1 | 1 | Q2 | | 2 | John | | 2 | 1 | Q7 | ----------------------------------------------------- ----------------------------------------------------------- This is my table structures. But there was only one table at the startup. My program reads all students and releated columns then creates second table. Is there a simply way that extract and transform these columns by a rule and load to the second table? Maybe in integration services. If there's a useful way it makes us very comfortly. Thanks for your replies.
View Replies !
View Related
Transposing Rows And Columns
I am trying to find a way to transpose rows and columns for a dataset. In otherwords I would like to take a dataset that looks like this col1 | col2 | col3 --------*---------*---------- Value 1 | Value 2 | Value 3 And change it to one that looks like this: Name | Value -----*--------- col1 | Value 1 -----*--------- col2 | Value 2 -----*--------- col3 | Value 3 Is there a T-SQL way to do this? Thanks, Jacob
View Replies !
View Related
SQL Query: Rows To Columns
Hi All, I have a sample table as below: (All columns do not have to have unique values) Column n is int, the rest are varchar [First row is the column Names] n | t1 | t2 | t3 | t4 1 | a | b | c | d 3 | e | w | a | t 3 | w | q | u | k 2 | g | w | q | n 2 | a | b | s | a 5 | n | a | w | h I need to create a query or some how create a way to get the data in the form of the below table: n | t1 | t2 | t3 | t4 | t11 | t21 | t31 | t41 1 | a | b | c | d | 3 | e | w | a | t | w | q | u | k 2 | g | w | q | n | a | b | s | a 5 | n | a | w | h What that means is that I have a huge table (~6000 rows) that has the column 'n' above (int type) which can have rows that start with the same number (such as number 3 & 2 above). I need these duplicate rows of numbers to be converted/transformed to columns alongside each other. This would create 1 entry for each type of number but have the 4 columns of t1, t2, t3, t4 'added' on as extra columns in the new table with a different column name ofcourse. So what that would mean is that if there were 3 rows of number n=2 then would have 3x4=12 columns, plus the 'n' column and so forth. I am not sure on how to start the query for this. What I have thought is maybe using a cursor and going through the table. But not sure how I could do that really. The other is using a Pivot in a SELECT statement. But that would not work since the columns can grow indefinetely depending on how many of the same 'number' row there is! Any ideas on this? Thanks a bunch !!! (running SQL 2005)
View Replies !
View Related
|
TRACKER
