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.
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.
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 Relatedhow 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]...
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
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 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!
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
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 RelatedBelow. 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.
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 RelatedHi 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 RelatedI 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 RelatedHello,
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.
I 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
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.
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 1 Replies View Relatedis it possible to write a query so that we can have all rows of one column in a single columnTIA
View 1 Replies View RelatedI 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
I 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
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
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
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
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 9 Replies View RelatedLets 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
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.
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
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
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 4 Replies View RelatedHello,
I trying to make columns from rows? How do I do that? I am using SQL Server 2000.
I thank in advance anyone capable of helping me.
E. Woods
I have data in a table. I want the values in the rows to place in columns and columns into rows.
Eg:-A table. It consists of three columns and three rows.
name id dept
a 1 x
b 2 y
c 3 z
I want the resultant table should look like this
a b c
1 2 3
x y z
Whether it's possible ?
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
Hi
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?