A Query-Transpose Of Data
Jul 23, 2005
I am supplying you with Sample Data:-
Initial Classcode SampleSize Average
------- ---------- -------------------------------
ADK SSC 22 3.6800000000000002
ADK TSC 17 2.7599999999999998
ADK TSM 5 3.5499999999999998
ANB FCA 31 3.23
ANB FCB 50 3.0499999999999998
ANB FCC 30 3.0899999999999999
ANB SCA 35 3.02
ANB SCB 9 3.4300000000000002
ANB TCA 30 2.77
ANB TCB 6 1.8799999999999999
APG MCH 10 3.8300000000000001
APG TSCH 9 4.21
AUG FCC 30 3.5499999999999998
AUG SCA 28 2.7800000000000002
AUG SCB 29 3.4300000000000002
AUG SCC 30 2.8999999999999999
AUG TCA 30 2.8599999999999999
AUG TCB 29 2.1200000000000001
AVK TSP 12 3.6200000000000001
BKK FS 32 2.52
BKK TSM 5 3.3799999999999999
BSK SSP 28 3.1200000000000001
BSK TSP 12 3.0600000000000001
------- ---------- -------------------------------
These are the averages of teachers grouped by initial.
Maximum 7 averages are related to each teacher.
Ignore the column SampleSize.
Using this output is it possible to get output like this:
Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..
ADKTSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..
ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..
APG.........................
AVK....................
BKK....................
BSK.....................
Since the maximum class nos are 7, those having less than classes will
contain NULL in the class and average field.
Is it possible to carry out this in single query?
-Sameer
View 1 Replies
ADVERTISEMENT
Jul 26, 2006
Hi Guys
I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,
I have data as follows...
FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..
Second problem is i need to take this data and transform it to look like the table below
RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut
411 07/23/200620:45:02iGuard# IN
421 07/23/200620:46:17iGuard# OUT
431 07/23/200620:48:08iGuard# IN
441 07/23/200620:48:18iGuard# OUT
451 07/23/200620:48:24iGuard# IN
461 07/23/200620:48:30iGuard# OUT
471 07/23/200620:48:36iGuard# IN
481 07/23/200620:48:41iGuard# OUT
501 07/23/200620:49:57iGuard# IN
511 07/23/200620:50:14iGuard# OUT
521 07/23/200620:59:34iGuard# IN
531 07/23/200620:59:40iGuard# OUT
Employee IDDateInOutIn OutTotalTimeIn
123/07/200620:3520:3620:3820:3900:02
Basicaly i need to transpose it..
If anyone has the sql or knows the sql on how to do this i would be very greatful
View 3 Replies
View Related
Jan 20, 2004
Hi all,
Help me out, i am trying to get all values in Table A and insert into table B, i though of writing cursor. see endof the message
Table A:
CREATE TABLE [dbo].[M_SCANNEDSURVEY_AP] (
[M_CustomerSurveyID] [bigint] NULL ,
[SurveyID] [bigint] NULL ,
[LoadStatus] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[7] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[8] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[9] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[10] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[11] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[12] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
Table B:
CREATE TABLE [dbo].[M_RESPONSE] (
[M_CustomerSurveyID] [bigint] NOT NULL ,
[SurveyID] [bigint] NOT NULL ,
[SeqNumber] [bigint] NOT NULL ,
[Response] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
I want to insert everything in table A to table B
M_CustomerSurveyID -> M_CustomerSurveyID
SurveyID -> SurveyID
I will hardcode insert 1,2,3,4,5,6,7,8,9,10,11,12 for sequence number
values of 1,2,3,4,5,6,7,8,9,10,11,12 -> Response
DateSubmitted -> DateSubmitted
Declare @Count_Scan INT
BEGIN
-- Get the count of scanned data in scanned data table with LoadStatus = N
SELECT @Count_Scan = COUNT(*) from M_SCANNEDSURVEY_APTEST where
LoadStatus = 'N'
IF @Count_Scan > 0
BEGIN
DECLARE ScanData_Cursor CURSOR FOR
SELECT * FROM M_SCANNEDSURVEY_AP WHERE LoadStatus = 'N'
OPEN ScanData_Cursor
FETCH NEXT FROM ScanData_Cursor
While (@@Fetch_Status <> -1)
Begin
If (@@Fetch_Status = -2)
Begin
FETCH NEXT FROM ScanData_Cursor
Continue
End
CLOSE ScanData_Cursor
DEALLOCATE ScanData_Cursor
END
SET @CountSuccess = 'Y'
END
View 4 Replies
View Related
Nov 3, 2014
I need to create a report out of our database.
Simplified example:
Table 1:
LINK_ID Item
123456 Item1
123457 Item2
123458 Item3
123459 Item4
Table 2
Link_IDPROP_IDProperty
1234561Property_1
1234562Property_2
1234563Property_3
1234564Property_4
[Code] ....
When I Join this 2 tables i get:
LINK_IDItemPROP_IDProperty
123456Item11Property_1
123456Item12Property_2
123456Item13Property_3
123456Item14Property_4
[Code] ....
But I need:
LINK_IDItemPROP_ID1PROP_ID2PROP_ID3PROP_ID4
123456Item1Property_1Property_2Property_3Property_4
123457Item2Property_1Property_2Property_3Property_4
123458Item3Property_1Property_2Property_3Property_4
123459Item4Property_1Property_2Property_3Property_4
View 3 Replies
View Related
Oct 4, 2006
I'm relatively new to this, so bear with me here. (SQL Server 2005 Express, Datatypes are all varchar, int or money, nothing crazy...)
I currently have a table (not designed by me...) which looks like this:
ProjectID Months Expenses
3214 JAN 45.67
3214 MAR 56.78
1234 JAN 78.99
4567 MAY 43.56
And so on.... And I need this:
Project ID Jan Feb Mar Apr May Jun etc....
3214 45.67 56.78
1234 78.99
4567 43.56
I had attempted to do it using this code (really sloppy... I know. Beginner's attempt...)
DECLARE @Months varchar(4)
DECLARE @Counter int
DECLARE @Rowcount int
EXEC @RowCount=dbo.ReturnRowCount
SET @Counter = 0
WHILE @Counter <= @RowCount
BEGIN
SET @Counter=@Counter+1
SET @Months=(SELECT Months FROM TestData WHERE RowNum=@Counter)
SELECT @Months
WHILE @Months='JAN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jan)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='FEB'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Feb)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Mar)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='APR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Apr)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAY'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, May)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jun)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUL'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jul)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='AUG'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Aug)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='SEP'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Sep)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='OCT'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Oct)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='NOV'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Nov)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='DEC'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Dec)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
SET @Months=''
END
SELECT RowNum, ProjectID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM SusansOutputTable
I also tried using IF statement instead of the imbedded WHILE statement and had the same result. What it's returning is 98,765 results of the same thing, with the exception of the last 25 rows, which return what I expected (which still needs to be collapsed...) I know there HAS to be an easier way to do this, I'm afraid it might be a bit beyond me though, any help?
Ps. ReturnRowCount function just returns the row count of the base table, assuming a rowid column with an IDENTITY int variable, which I can safely assume in this case. Also, the counter seems to work fine, but something is wrong in my internal portion of the while loop I think...
View 3 Replies
View Related
Jul 15, 2015
I have following sample data
I want this data updated to different table based on PK value relationship like below,
What shall I do to make such update ? Here is the sample script I want ot use
 DECLARE @Activity TABLE(PK BIGINT,Value1 NVARCHAR(10),Value2 NVARCHAR(10))
 INSERT INTO @Activity(PK) VALUES(1)
 INSERT INTO @Activity(PK) VALUES(2)
 INSERT INTO @Activity(PK) VALUES(3)
[Code] .....
View 11 Replies
View Related
Jan 19, 2015
In our contract management system, each contract has over 100 reference fields attached to it. These are all stored in single table with contract ID, reference GUID and value as the columns.
So you will have multiple rows for each contract....one for each of the reference fields and then the value attached to that reference.
I want to return the data so there is one row per contract with the reference fields as columns and the reference field values as the column data.
Can this be done using PIVOT as I have tried but not had any success?
View 6 Replies
View Related
Jun 1, 2015
I have a simple table of yearly financials to transpose:
Year/Field      Financial1 Financial2 Financial3...
2014Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2013Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
2012Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000000Â Â 2000000Â 3000000
....
The number of years available is dynamic. I would like to retrieve it as follows:
                    2014          2013       2012    Â
.....
Financial1Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial2Â Â Â Â 1000000Â Â 2000000Â 3000000
Financial3Â Â Â 1000000Â Â 2000000Â 3000000$
What is the simplest way to achieve this?
View 7 Replies
View Related
Apr 1, 2014
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
View 7 Replies
View Related
May 2, 2008
Hello,
I have a table that looks like this:
Customer Treas
------- -----
1010 Merch
1010 Price
1020 Merch
1030 Promo
And I need to add a period column so that the table looks like this.
Customer Treas Period
------- ----- ------
1010 Merch 1
1010 Merch 2
1010 Merch 3
1010 Price 1
1010 Price 2
1010 Price 3
1020 Merch 1
1020 Merch 2
1020 Merch 3
1030 Promo 1
1030 Promo 2
1030 Promo 3
etc. Of course there are 12 periods.
I'm thinking this is related to cross-tab or transpose function somehow? Once again I am clueless to the point I find it difficult to even research the problem on my own.
Hopefully this makes sense.
Thanks!
View 1 Replies
View Related
Apr 26, 2001
Besides using a cursor, is there a way to transpose a short result set to a comma delimited list?
For example, assume SELECT color FROM [tblColor] yields:
Red
Green
Blue
Black
My desired result is really:
Red, Green, Blue, Black
Any clues?
View 1 Replies
View Related
Feb 25, 2004
Hello
I am currently importing an excel table into sql server in the form
date code header1 header2
2004 5AX num1 num2
2004 5AZ num3 num4
......
I am reading this into an sql table fine. However I want to convert its format into the following
date code field value
2004 5AX header1 num1
2004 5AX header2 num2
2004 5AZ header1 num3
2004 5AZ header2 num4
......
The number of header1, header2,..... is dynamic so I cannot hard code the number of headers there are.
Has anyone got any ideas. I have got some code already which will give me the number of headers there are but that Im afraid is as far as I have got before I get stuck
Thanks in advance for any help
Richard
View 2 Replies
View Related
Mar 8, 2007
How to transpose a table. My table is like
taskid date1 hrs1 date2 hrs2 date3 hrs3 emp
1 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 AAA
2 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 BBB
I want the result as
date1 date2 date3
emp taskid hrs1 hrs2 hrs3
View 5 Replies
View Related
Jul 23, 2005
I'm playing around with the following tables and need some advice.TablePeople (5000 records)peopleIDsexageraceTableExam (5000 records)examIDdatepeopleIDTableQuestions (5000 records)questionIDquestionFieldname (QFN0001,QFN0002,QFN0003, ... ,QFN5000)TableAnswers (25,000,000 records)examIDquestionIDanswerSQL Query Statement Needed!Query the database to return fields from TablePeople and TableAnswers(from 1 to 255 questionFieldnames)Example Query and return data from:ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011I know how to join the tables and return the following recordsetExamID, sex, age, race, QuestionFieldName, AnswerID100001,M,40,White,QFN0001,0100001,M,40,White,QFN0002,5100001,M,40,White,QFN0003,6100001,M,40,White,QFN0005,3100001,M,40,White,QFN0007,5100001,M,40,White,QFN0011,4100002,M,55,White,QFN0001,1100002,M,55,White,QFN0002,4100002,M,55,White,QFN0003,3100002,M,55,White,QFN0004,4100002,M,55,White,QFN0007,5100002,M,55,White,QFN0011,6etc105000,M,48,White,QFN0001,5105000,M,48,White,QFN0002,4105000,M,48,White,QFN0003,6105000,M,48,White,QFN0005,3105000,M,48,White,QFN0007,4105000,M,48,White,QFN0011,5We would like the resulting table to look like the following:ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011100001,M,40,White,0,5,6,3,5,4100002,M,55,White,1,4,3,4,5,6etc105000,M,48,White,5,4,6,3,4,5Any suggestions appreciated.Gregory S. MoyInformation Processing ConsultantEpiSense Research ProgramDepartment of Ophthalmology & Visual SciencesUniversity of Wisconsin - Madison
View 1 Replies
View Related
Dec 11, 2007
Is there anything like transpose in SSRS.
The result of my t-sql queries gives me many columns and less rows
i.e there are four quaters as rows and there are around 10 columns like sales , customer , material ....
So if i can transpose columns to rows and rows to columns some how then the report will be more presentable according to me.
Can anyone please help me out.
View 1 Replies
View Related
Mar 30, 2007
Hi
I have a "transaction" table of diagnostic tests, each row consisting of one test performed on one person as follows:
Patient Test Time
ID ID of Test
--------------------------
1 1 May 1
1 2 May 1
1 4 May 2
2 5 etc.
3 1
3 3
4 4
4 2
I'd like to transpose this into a table with one row per patient, so I can then describe, for each patient, the sequence of tests that he or she had, e.g.
Patient T1 T2 T3 T4 T5 TEST_SEQUENCE
1 1 2 4 - - "1 2 4"
2 5 - - - - "5"
3 1 3 - - - "1 3"
4 4 2 - - - "4 2"
How do I do this in SQL, preferably without having to specify the maximum number of tests? (In a statistical package we use, this is called a table "reshape" operation)
Thanks in advance.
View 7 Replies
View Related
Oct 22, 2007
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 3 Replies
View Related
Apr 23, 2008
I have the below table:
Pallet1 ItemA1
Pallet1 ItemB1
Pallet1 ItemC1
Pallet1 ItemD1
Pallet1 ItemE1
Pallet2 ItemA2
Pallet2 ItemB2
Pallet2 ItemC2
Pallet2 ItemD2
Would like to Transpose it to
Pallet 1 ItemA1 ItemB1 ItemC1 ItemD1 ItemE1
Pallet 2 ItemA2 ItemB2 ItemC2 ItemD2 NULL
Would be thankful for Any help.
View 2 Replies
View Related
Jul 2, 2014
I am looking to transpose a row into multiple columns. I have a member's data who might be associated with multiple labs for that one member. Instead of having multiple records for that member, I would like to have one record per member with multiple rows for the lab data.
View 16 Replies
View Related
Mar 30, 2006
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 3 Replies
View Related
May 29, 2007
Hi Friends,How to transpose the columns into rowsi want to convert the table which looks like thisID Name HomePhone WorkPhone Email1 test1 678364 643733 Join Bytes!2 test2 678344 643553 Join Bytes!to a table which should look like thisID Name Device1 test1 6783641 test1 6437331 test1 Join Bytes!2 test2 6783442 test2 6435532 test2 Join Bytes!Thanks in AdvanceArunkumar
View 1 Replies
View Related
Mar 23, 2015
how to transpose rows to columns . there are 800 dynamic rows
device_idUserNameipaddrmacaddrLastConnecttimeOperatingSystemRAMCPUTOTALSIZEFREESPACEpercentageDriveNameName
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 ActiveX [15.0.0.239]
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 Plugin [15.0.0.239]
[code]...
View 9 Replies
View Related
Jun 29, 2015
I wondered if it is possible to convert/transpose a list into a 1 row. I have attached a small sample of data and what outcome I would like.
View 19 Replies
View Related
Oct 29, 2015
I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.
I have data in the form of:
Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,
The output should look like:
Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black
View 2 Replies
View Related
Jul 20, 2005
I have a table like the followingField1 Field2 Field3------ ------- ------x1 y1 z1x1 y2 z2x1 y3 z3x1 y4 z4x2 y1 z5x2 y2 z6x2 y3 z7x2 y4 z8x3 y1 z9............and so onI want to create a view with x1, x2, x3.. as uniquerecords; y1, y2, y3.... as fields; and z1, z2, z3.... as the valuesWhen I doCREATE VIEW xyz (y1, y2, y3, y4) ASSELECT field1 ,( SELECT field3 FROM table WHERE field2 = 'y1'),( SELECT field3 .....FROM tableI get the error that the sql query creates duplicate values. I think Imay have to do a join using distinct values of field1. I was lookingfor some guidance with the join.Thanks for your help in advance(using SQLSERVER 2000)
View 2 Replies
View Related
Apr 23, 2008
How can i transpose rows value as Column?
Table A
TypeID
Payment Type
1
CASH
2
EFPOS
3
BANK CARD
4
VISA
5
AMEX
6
DINNER
NOTE: User can add some more Payment type
Required Output
CASH
EFPOS
BANK CARD
VISA
AMEX
DINNER
€¦
Thanks
DA
View 5 Replies
View Related
Aug 15, 2015
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payer varchar (5), Â Â Â Â Â Â Â Â Â
ID varchar (5), Â Â Â
ClM varchar (2), Â Â Â Â Â Â Â Â Â
Paid1 float,
Paid2 float,
Paid3 float
) Â Â Â Â Â Â Â Â Â Â Â
[code]....
Desired result:
Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
 001 002
76.58 19.53
 153.48 96.43
 53.48  200
View 10 Replies
View Related
Jun 26, 2006
I have something of a challenge for you folks (at least it's very challenging for me).
I have a table that has data that looks like this:
Date______OrderNum____WorkDay
2006-06-1__AA_________1
2006-06-1__AB_________1
2006-06-2__BA_________2
2006-06-2__BB_________2
2006-06-2__BC_________2
2006-06-5__CA_________3
2006-06-5__CB_________3
etc.
So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.
What I am trying to write is a SQL statement that will display results thus:
Day1______Day2______Day3_______etc.
AA________BA_______CA_________etc.
AB________BB_______CB_________etc.
Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.
I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!
View 7 Replies
View Related
Apr 29, 2015
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
Current result set:
TotalAllCustomersOnFile | TotalConsumersWithValidEmail | TotalConsumersWithValidEmailAndOptedIn
2,500 1,750 1,500
Desired result set:
Audience | Totals
----------------------------------------------------
TotalAllCustomersOnFile | 2,500
TotalConsumersWithValidEmail | 1,750
TotalConsumersWithValidEmailAndOptedIn | 1,500
View 3 Replies
View Related
Aug 15, 2015
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,
[Code] ....
Desired result:
Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200
View 6 Replies
View Related
Nov 12, 2014
I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.
View 6 Replies
View Related
Aug 31, 2015
Below. I have also pasted the current result of this query and the desired result.Â
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
View 2 Replies
View Related
Jun 15, 2007
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
View 1 Replies
View Related
