Converting Rows Into Columns
Sep 20, 2004Hi
How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?
ADVERTISEMENT
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Dec 25, 2005Hello,
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.
Converting Rows Into Columns
Jun 22, 2008hi,
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
Converting Rows To Columns
Jul 20, 2005have 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 7 Replies View RelatedConverting Rows Into Columns MS SQL 2K
Jul 20, 2005I 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 1 Replies View RelatedConverting Rows Into Columns
Mar 10, 2008
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?
Converting Rows To Columns In SQL7
Jul 20, 2005I 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 2 Replies View RelatedConverting Rows To Columns Using Case.Problem With
Mar 28, 2008SELECT
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)
Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...
Sep 19, 2006This 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 8 Replies View RelatedPIVOT/CROSS TAB/Converting Rows To (multiple Group) Columns
Aug 3, 2007Hello 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.
T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns
Jun 11, 2015Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
T-SQL (SS2K8) :: Rows Into Columns - Remove Duplicates And Variable Rows
Aug 5, 2014I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
Turn Columns Into Rows And Rows Into Columns
Jan 24, 2008I 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.
Converting Nrows Into Columns
Sep 25, 2007Hi,
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
Converting Columns In An INSERT (was SQL Query)
Jan 17, 2005I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)
Any pointers much appreciated...
Converting Multiple Records Into Columns
Oct 11, 2013I have a table called DISTRIBUTION_SL in which there will multiple records for each request as shown below in the data.I would like to convert the recepient email into columns and then join with other tables so that the entire request information is seen in one row rathar than multiple rows
Table
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CREATE TABLE [dbo].[DISTRIBUTION_SL](
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NOT NULL,
[recipnm] [varchar](50) NULL,
[emailadr] [varchar](75) NULL,
[code]....
T-SQL (SS2K8) :: Converting Rows Into Column
Apr 18, 2014It is possible to covert rows into column by using tsql script, see attached file for more details...
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, ItemNo int)
GO
INSERT INTO Table1
SELECT '01', 'Camera', 100, 1
UNION ALL
SELECT '01', 'Memory 4GB', 10, 2
[code]....
Converting String Into Rows Query?
Sep 19, 2014I have a string like below
DECLARE @STR VARCHAR(100) = 'AAAAAA~KKKKK~LLLL~AAAA'
i want to convert the string into rows like
AAAAAA
KKKKK
LLLL
AAAA
Problem Converting Rows To A String
Jul 23, 2005I 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 5 Replies View RelatedConverting Rows Records Into Table Fields
Feb 21, 2002Hi 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.
Transact SQL :: Converting Rows To Column And Getting Data
Jul 22, 2015I have a sql table like this
event_id timestamp event_name event_score
1 3000 alarm 0.95
10 3500 alarm 0.85
5 4000 alarm 0.93
20 4200 alarm 0.87
30 5000 alarm 0.87
30 8000 alarm 0.97
40 9000 alarm 0.98
13 9700 alarm 0.98
And I am expecting output like this(With multiple rows)
event_id1 event_id2 event_id3 event_name event_score1 event_score2 event_score3
1 10 5 alarm 0.95 0.85 0.93
30 40 13 alarm 0.97 0.98 0.98
Also I have certain conditions like the timestamp difference between event_Id1 and event_Id3 < 3600 eg 1 hour
Columns Display Format While Converting Data Into Excel And PDF
Nov 11, 2007Hi,
I'm using SSRS to generate reports. i have many columns data to be displayed.while converting the data into Excel and PDF the data, header, and footer are not displaying proper format. what are all the properties to be set for that.
Could any one help in this regard.
Thanks for your help..
Converting Repeating Fields In Single Row To Multiple Rows
Sep 4, 2007Hi 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
SQL Server 2008 :: SSIS Derived Column Transformation Failing On Converting Blank Rows
Jul 30, 2015I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.
View 7 Replies View RelatedColumns Vs. Rows
May 10, 2007hi,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 1 Replies View RelatedRows As Columns
Oct 6, 2005is it possible to write a query so that we can have all rows of one column in a single columnTIA
View 1 Replies View RelatedRows Become Columns Help!
Dec 5, 2005I 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
Columns To Rows And A Sum
Nov 19, 2013I am using SQL 2008. I have a database called ISCmetrics and a table called Meeting, the table meeting has 5 columns id
( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar)
We have around 20 different team names , and every team enters data into the database every day, and we have around 7 Different STATUSNAME they are always the same , the STATUSLEVEL is always a 1 or a 0 , so TEAMA enters a value every day for each of the 7 STATUSNAME, and the value is either a 1 or a 0 .
The output for a Select * from ISCMetricslooks like this
id TEAMNAME DATECOMPLETE STATUSNAME STATUSLEVEL
1 TeamA 20131022 STATCONTACT 1
2 TeamA 20131022 STATACTION 1
3 TeamA 20131022 STATABC 1
4 TeamB 20131022 STATCONTACT 1
5 TeamB 20131022 STATCTION 0
6 TeamB 20131022 STATABC 1
7 TeamA 20131021 STATCONTACT 0
8 TeamA 20131021 STATACTION 1
9 TeamA 20131021 STATABC 0
10 TeamB 20131021 STATCONTACT 1
11 TeamB 20131021 STATACTION 1
12 TeamB 20131021 STATABC 1
What i am trying to achieve is this, i want the teams in one column then a column for each of the dates , and then sum of the STATUSLEVEL in each row as shown below for the day and team .....
TEAMNAME 20131022 20131021
TeamA 3 1
TeamB 2 3
Rows Become Columns
Jan 18, 2007hello 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
Rows To Columns
Jun 9, 2007Afternoon
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
Columns Into Rows
Feb 29, 2008Hi,
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
Rows As Columns
Jul 20, 2005I 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 9 Replies View RelatedColumns To Rows
May 26, 2006Lets 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