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:
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
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
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...
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)
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?
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$
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):
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.
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
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
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.
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)
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.
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 ?
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
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]
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)
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
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.
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.
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
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.
