Using Select top 1 statement I have to retrieve data from an 8 column table without any unique key.  Now I want to transpose this table into two columns: original column name to the first and its respective value to second.  An original table like this:  Column1 | Column2 | Column3 | Column4  55      | 108     | 555     | 85   And the expected result:  ColumnName | Value Column1    | 55 Column2    | 108 Column3    | 555 Column4    | 85
120564
I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005.  my input Data    ONAY_ID  sip_RECno    KULLANICI       ONAY    1           1         user1          true    2           1         user2          true    3           1         user3          false    4           1         user4          true   Output would be   sip_RECno  user1     user2    user3     user4           1          true     true    false    true
61628
I would like to convert several range of columns into rows, and keeping the association to the original col/row source  Sample source sheet  --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days | --------------------------------------------------- | name 1  |    25    |   30     |    5     | etc...|  --------------------------------------------------- | name 2  |    2     |          |    4     | etc...| --------------------------------------------------- | etc ... 100 rows   Expected Results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- | name 1  |  1/2/2018   |   30     |    --------------------------------------------------- | name 1  |  1/3/2018   |   5      |    --------------------------------------------------- | name 1  |  etc.. 365 days --------------------------------------------------- | name 2  |  1/1/2018   |   2      |    --------------------------------------------------- | name 2  |  1/2/2018   |          |    --------------------------------------------------- | name 2  |  1/3/2018   |   4      |    --------------------------------------------------- | name 2  |  etc.. 365 days --------------------------------------------------- | etc ... for each change of name pull all the col dates/values   Current results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- |         |  1/2/2018   |   30     |    --------------------------------------------------- |         |  1/3/2018   |   5      |    --------------------------------------------------- |         |  etc.. 365 days                      I have to copy down to bring in "name 1" for each date  --------------------------------------------------- | name 2  |  1/1/2018   |   2      |             now update the formula to bring in the second item for the sequence of dates --------------------------------------------------- |         |  1/2/2018   |          |    --------------------------------------------------- |         |  1/3/2018   |   4      |    --------------------------------------------------- |         |  etc.. 365 days --------------------------------------------------- | etc ...     =Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates.  How can I have it match the number of dates?  and be dynamic to update as dates/values are added Edit: trying to use split and rept to count the number of times to repeat, then transpose.. =transpose(rept(split(arrayformula(concatenate(Sheet1!A:A&amp;"1")),"1",True, True),COUNTA(Sheet1!I1:ZZ1)))  =TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range.  How can I make it dynamic to import dates as they are added?
58142
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.  CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford   Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.  1, Dan, Mazda+Suzuki 2, John, Ford 3, Dasha, Volvo+Ford   Thanks
48546
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this  trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.  The data looks like this:  id  row_num  customer_code comments ----------------------------------- 1   1        Dilbert        Hard 1   2        Dilbert        Worker 2   1        Wally          Lazy   My results need to look like this:  id  customer_code comments ------------------------------ 1   Dilbert        Hard Worker 2   Wally          Lazy   So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.   My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.
45653
I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with multiple key staff names listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one business per row. The number of lines in the 'key staff' cell varies row-by-row.  My initial sheet looks like this:    I need to do the following to optimize this sheet:   split each multi-line ‘key staff’ cell so each key staff name appears on its own row. This requires that new row/s be inserted beneath the original row. duplicate the data from all other cells on the original row (i.e. from columns A &amp; C:E), so that each new row contains the full data for each business I need an automated process - I'll have about 1000 businesses to process so can't do this with any manual steps   The sheet should then look like this:    Using =TRANSPOSE(SPLIT(B1,CHAR(10))) obviously only goes part of the way - it doesn’t insert new rows and doesn’t duplicate the surrounding column entries. All help gratefully accepted!
25484
I have a MySQL table with coordinates, the column names are X and Y. Now I want to swap the column values in this table, so that X becomes Y and Y becomes X. The most apparent solution would be renaming the columns, but I don't want to make structure changes since I don't necessarily have permissions to do that.  Is this possible to do with UPDATE in some way? UPDATE table SET X=Y, Y=X obviously won't do what I want.    Edit: Please note that my restriction on permissions, mentioned above, effectively prevents the use of ALTER TABLE or other commands that change the table/database structure. Renaming columns or adding new ones are unfortunately not options.
110578
I have a spreadsheet in Google Docs with two data rows and lots of columns like so:    Is it possible to easily convert these rows into two columns and lots of rows (obviously retaining all information and formulas)? In other words, to something like this:   19.9.2009   967,15 €  28.9.2009   950,78 € 12.11.2009   942,03 € ..           ..   There's probably a neat mathematical term for such a conversion, but I don't remember which (matrix something?). Feel free to suggest a better name for the question.  Edit: To clarify, ultimately I want to get rid of the original (two row) version and continue working on the converted one. TRANSPOSE(), , got me very close but not quite there. I want the raw data in the transposed version, not cells that mirror the original ones.
2771
Consider the following table:  id  title   genre1  genre2  genre3  genre4  genre5 1   Movie1  Thriller    Crime   NULL    NULL    NULL 2   Movie2  Fantasy NULL    NULL    NULL    NULL 3   Movie3  Political   Philosophical   Historical  NULL    NULL 4   Movie4  Science Fiction NULL    NULL    NULL    NULL   When I unpivot genre1, genre2, ... the NULL values are removed, so that, for example, the rows for Movie1 would be:  id title  genre     original_column 1  Movie1 Thriller  genre1 1  Movie1 Crime     genre2   Is there a way to include NULL values, for example:  id title  genre     original_column 1  Movie1 Thriller  genre1 1  Movie1 Crime     genre2 1  Movie1 NULL      genre3 1  Movie1 NULL      genre4 1  Movie1 NULL      genre5   I can SELECT ISNULL(genre1, '*'), ISNULL(genre2, '*') ..., but suppose I want the values to appear as NULL? Is there a better way than:  WITH  -- SELECT that unpivots table AS unpivoted SELECT unpivoted.id,    unpivoted.title,    ,CASE unpivoted.genre WHEN '*' THEN NULL ELSE unpivoted.genre END    ,unpivoted.original_transaction FROM unpivoted   Also, I notice that the query that returns the source data to be unpivoted MUST have an alias. For example:  SELECT ... FROM   -- *must* give it an alias or it's a syntax error   (SELECT ... FROM) AS source    UNPIVOT(genre for original_column IN (genre1,genre2, genre3,genre4,genre5))   Does anyone know why? I have not found a way to refer to the alias anywhere in the query.
3484
I have data that looks like this:  Id | Loc1 | Loc2 | Loc3 | Loc4 ---+------+------+------+----- 1  | NY   | CA   | TX   | IL 2  | WA   | OR   | NH   | RI   And I want to convert it to this:  Id | LocNum | Loc ---+--------+---- 1  |   1    | NY 1  |   2    | CA 1  |   3    | TX 1  |   4    | IL 2  |   1    | WA 2  |   2    | OR 2  |   3    | NH 2  |   4    | RI   What's the easiest way to do this in Excel 2007?
117172
A typical database table might look something like this:  date-------geography---clicks---cost---conversions ___________________________________________________ 1/1/2010---Kansas------56-------12-----1   When transposed, it looks like this.  date-----geography---metric_name---metric_value ____________________________________________ 1/1/2010-Kansas------clicks--------56 1/1/2010-Kansas------cost----------12 1/1/2010-Kansas------conversions---1   The transposed table works better for some use cases where the number of metrics being collected changes frequently.  Is there a standard name for a table structured like that in the transposed table?
114266
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.  Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:  Type    Person  Count Type A  Person 1    10 Type A  Person 2    91 Type A  Person 3    13 Type A  Person 4    10 Type A  Person 5    2 Type A  NULL        126 Type B  Person 6    16 Type B  Person 7    63 Type B  Person 8    15 Type B  Person 9    22 Type B  Person 10   16 Type B  Person 11   3 Type B  Person 12   20 Type B  Person 13   44 Type B  NULL        198 NULL    NULL        360   Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"  And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."  Thanks!
24577
For example, I want the first entry in Column B (B1) to show A5, then B2 shows A10, then B3 shows A15.  How would I go about doing this?
18362
I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-clustered index.   By changing the index from clustered to non-clustered, does this make any guarantees about the order in which rows would appear in the table?  This question is mostly for my curiosity; I am going to suggest using an identity column instead, but this request got me thinking. Timestamp could be used but there is a chance rows can be inserted simultaneously.   Thanks in advance for your help.
95354
I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It does not matter in what order the new table is.      A   B   C   D            A   B   C            A   B   C     1      H1  H2  H3        1  H1  R1  V1        1  H1  R1  V1  2   R1 V1  V2  V3   =&gt;   2  H1  R2  V4   or   2  H2  R1  V2  3   R2 V4  V5  V6        3  H1  R3  V7        3  H3  R1  V3  4   R3 V7  V8  V9        4  H2  R1  V2        4  H1  R2  V4                           5  H2  R2  V5        5  H2  R2  V5                           6  H2  R3  V8        6  H3  R2  V6                           7  H3  R1  V3        7  H1  R3  V7                           8  H3  R2  V6        8  H2  R3  V8                           9  H3  R3  V9        9  H3  R3  V8   I've been playing around with the OFFSET function to create the whole table but I feel like a combination of TRANSPOSE and V/HLOOKUP is required.  Thanks  EDIT  I have managed to come up with the correct formulas. If the data is in Sheet1 like in my example above, the formulas go in Sheet2:  [A1] =IF(ROW() &lt;= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")  [B1] =IF(ROW() &lt;= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"")  [C1] =IF(ROW() &lt;= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")   The formulas are limited to B1:Z1 for the headings and A2:A99 for the rows (these can be increased to their maximums if required). The COUNTA() formula returns the number of cells that actually have values, which limits the number of rows returned to headings*rows. Otherwise the formulas would could go on for infinity because of the MOD function.
46162
I need to have one column as the primary key and another to auto increment an order number field. Is this possible?  EDIT: I think I'll just use a composite number as the order number. Thanks anyways.
119155
I have an existing table with data:  dbo.Test (col1,col2,col3....) ON [PRIMARY]   I need to change this table to be partitioned like this:  dbo.Test(col1,col2,col3....) ON Ps_Date(Col2)   How I can I achieve this without dropping and recreating the table?
88547
I can select all the distinct values in a column in the following ways:   SELECT DISTINCT column_name FROM table_name; SELECT column_name FROM table_name GROUP BY column_name;   But how do I get the row count from that query? Is a subquery required?