I hope someone can help me with my problem. I have searched the
internet for days for a solution, but nothing i found seemed to work.
The following is what i have now:
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
Hello, 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.
;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 ..
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
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
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.
I have a set of data coming in from a text file that looks like:Date ID Value01/01/2005 1 Value101/01/2005 2 Value201/01/2005 3 Value301/01/2005 4 Value401/01/2005 5 Value501/01/2005 6 Value601/01/2005 7 Value701/02/2005 1 Value101/02/2005 2 Value201/02/2005 3 Value301/02/2005 4 Value401/02/2005 5 Value501/02/2005 6 Value601/02/2005 7 Value7There are 450 "ID"s per day and there will be ~30 days per month thatrepeat these 450 IDs and values. I could potentially reduce this tobetween 80 and 100 IDs. I would like to create a table that lookslike:Date ID1 ID2 ID3 ID4 ID5 etc...01/01/2005 Value1 Value2 Value3 Value4 Value501/02/2005 Value1 Value2 Value3 Value4 Value501/03/2005 Value1 Value2 Value3 Value4 Value5What is the best way to do this? Any built-in SQL commands to assist?Any tricks with DTS?Thanks!Dave
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!
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)
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)
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.
I 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!
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
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 .....
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:
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 :
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
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
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.
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?
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.
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
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?
There are multiple Type's for some AcctNbr's and what I want to do is run a query on the database so that if the AcctNbr has multiple Type's and CodesValue's it takes them and creates new columns like so:
Code:
AcctNbr MailCode_90 MailCode_99 SEG1 4 90 99 O
So on and so forth. There are multiple Type's and multiple codes that I need to do this with for each account number. If someone could give me a base code to try I could start somewhere. I am an SQL novice. Thanks.
