Transposing?
Jul 23, 2005
I have a set of data coming in from a text file that looks like:
Date ID Value
01/01/2005 1 Value1
01/01/2005 2 Value2
01/01/2005 3 Value3
01/01/2005 4 Value4
01/01/2005 5 Value5
01/01/2005 6 Value6
01/01/2005 7 Value7
01/02/2005 1 Value1
01/02/2005 2 Value2
01/02/2005 3 Value3
01/02/2005 4 Value4
01/02/2005 5 Value5
01/02/2005 6 Value6
01/02/2005 7 Value7
There are 450 "ID"s per day and there will be ~30 days per month that
repeat these 450 IDs and values. I could potentially reduce this to
between 80 and 100 IDs. I would like to create a table that looks
like:
Date ID1 ID2 ID3 ID4 ID5 etc...
01/01/2005 Value1 Value2 Value3 Value4 Value5
01/02/2005 Value1 Value2 Value3 Value4 Value5
01/03/2005 Value1 Value2 Value3 Value4 Value5
What is the best way to do this? Any built-in SQL commands to assist?
Any tricks with DTS?
Thanks!
Dave
View 1 Replies
Jul 20, 2005
Hello!I need to transpose some columns into rows and rows into columns. Iknow, tha i can do it by cursor, but i don't know how make it...I read a lot about it, but still don't understand...Can someone help me?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 8 Replies
View Related
Mar 12, 2008
hi there, i've got a Maconomy table that needs to be normalised. I'm fairly new to T-SQL and was hoping i could get advice on this forum. Possibly copy the below tables into Excel to make better sense.
i would like to PiVot/transpose the below table to so that the 8 types of HEADER are the columns but include the NOTENUMBER (i.e. this below table would give an output of 2 rows)
any takers?
NOTENUMBERHEADERAMOUNT1STRING1DATE1
1100030Insurance cost44
1100030Leasing cost300
1100030PropertyID0N3100023424
1100030PropertyModel0Nokia 3100
1100030PropertyName0Cell Phone
1100030PropertyYear0 2003.01.11
1100030Scheduled end date0 2004.12.20
1100030Tax cost463
1100031Insurance cost50
1100031Leasing cost400
1100031PropertyID0N3100023424
1100031PropertyModel0Siemens
1100031PropertyName0Cell Phone
1100031PropertyYear0 2003.01.11
1100031Scheduled end date0 2004.12.20
1100031Tax cost500
View 14 Replies
View Related
Jul 31, 2006
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 5 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
Mar 3, 2008
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 3 Replies
View Related
Jun 5, 2015
My table structure is like
col1 col2 col3 col4 col5 col6
abc. def. 3fg. 59j. 567. 596040
abc. def. 3fg. 59j. 567. 596042
abc. def. 3fg. 59j. 567. 596043
abc. def. 3fg. 59j. 567. 596044
edf. ijk. rkl. 1fh. 567. 596045
edf. ijk. rkl. 1fh. 567. 596046
edf. ijk. rkl. 1fh. 567. 596047
edf. ijk. rkl. 1fh. 567. 596048
edf. ijk. rkl. 1fh. 567. 596049
And I am trying to get the above data , gel them ino col 6 by comma separated
col1 col2 col3 col4 col5 col6
abc def 3fg 59j 567 596040,567 596042,567 596043,567 596044
edf ijk rkl 1fh 567 596045,596046,596047,596048,596049
Can I get an example query for this...
View 1 Replies
View Related
Nov 27, 2006
Dear All,I'm attempting to create a query that will transpose repeated fieldsinto a single table structure. Can anyone think of how this can be doneas I'm stumped at the minute? I'd like to do this without having tocreate a cursor due to the overheads and performance issues associatedwith cursors. The table may also include additional fields which I'mnot interested in.Serial Data is like this.............IkeyIval-----------------------------------------------RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName1RAF_LASTNAMEtestLastname1RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName2RAF_LASTNAMEtestLastname2....Transposed into table like this ..............EmailFirstnameLastname--------------------------------------------------------------------------Join Bytes!testFirstName1testLastname1Join Bytes!testFirstName2testLastname2....Any help, much appreciated ...Kind Regards,Tim-------------------------------------------------------------------------------------NOTE: these create temporary tables ....DECLARE @XML TABLE(ikey VARCHAR(200),ival VARCHAR(1000))INSERT INTO @XMLSELECT 'RAF_EMAIL', 'testemail1@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName1'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname1'UNION ALLSELECT 'RAF_EMAIL', 'testemail2@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName2'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname2'UNION ALLSELECT 'FORM_CATEGORY', 'nothing'UNION ALLSELECT 'NO_DOGS', '1'DECLARE @RESULTS(EMAIL,FIRSTNAME,LASTNAME)
View 1 Replies
View Related
Feb 9, 2015
There are 2 tables
1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID
Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458
Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB
Desired output is
TempTable
TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA
View 2 Replies
View Related
