score:4
Accepted answer
You can transform this data using the PIVOT
function in SQL Server. In order to PIVOT
the data, you will want to create your new column using the row_number()
.
If you have a known number of values, then you can hard-code the query:
select *
from
(
select address, id,
'id_'+cast(row_number() over(partition by address
order by id) as varchar(20)) rn
from yourtable
) src
pivot
(
max(id)
for rn in ([id_1], [id_2], [id_3], [id_4])
) piv
But if the values are unknown then you will need to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ','
+ QUOTENAME(rn)
from
(
select 'id_'+cast(row_number() over(partition by address
order by id) as varchar(20)) rn
from yourtable
) src
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT address,' + @cols + ' from
(
select address, id,
''id_''+cast(row_number() over(partition by address
order by id) as varchar(20)) rn
from yourtable
) x
pivot
(
max(id)
for rn in (' + @cols + ')
) p '
execute(@query)
The result of both queries is:
| ADDRESS | ID_1 | ID_2 | ID_3 | ID_4 |
-------------------------------------------
| 12AnyStreet | 1234 | 1235 | 1236 | 1237 |
More questions
- SQL Server - Rows to Columns without Aggregation
- SQL Server How To Transpose Rows To Columns, without PIVOT or UNPIVOT or Aggregation
- SQL Server - How to delete some rows of columns without disrupting the rest of the record
- Rows to columns without PIVOT in SQL Server
- Convert wide to long structure in SQL Server without aggregation on two columns
- Pivot Rows To Columns In SQL Server Without AVG or SUM
- Transpose rows to columns as it is in SQL Server (T-SQL) or SSIS without losing data
- How can I turn a bunch of rows into aggregated columns WITHOUT using pivot in SQL Server 2005?
- Efficiently convert rows to columns in sql server
- Convert Rows to columns using 'Pivot' in SQL Server
- SQL Server : Columns to Rows
- Select Rows with matching columns from SQL Server
- Combine multiple rows into multiple columns dynamically in SQL Server
- Converting Columns into rows with their respective data in sql server
- Ranking rows using SQL Server Rank function without skipping a rank number
- Create a table from CSV columns in SQL Server without using a cursor
- Transpose a set of rows as columns in SQL Server 2000
- SQL Server convert columns to rows
- How to convert columns to rows in sql server
- How do i transform rows into columns in sql server 2005
- Splitting Comma separated values in columns to multiple rows in Sql Server
- Create columns from rows in SQL Server
- SQL Server deadlock when only inserting new rows without performing any selects
- How to convert multiple rows into one row with multiple columns using Pivot in SQL Server when data having NULL values
- SQL Server rows to columns
- SQL Server : how to transpose rows into columns
- Convert rows to columns with condition in SQL Server 2017
- Dataset Update without Locking SQL Server 40,000+ Rows
- Convert two columns into 5 rows in sql server
- SQL Server 2016 count similar rows as a column without duplicating query
More questions with similar tag
- Maximum of the count of the grouped elements
- django - convert mssql datetime to separate date and time
- De-normalizing data into a code first Entity Framework entity, without a SQL View
- How to reorder rows using SQL (not swap)
- Deduplicating SQL data per row
- How to use use if..else in Data Flow based on user variable values in SSIS
- Dimensional Modeling: should a fact table have a foreign key?
- Sql Query with Date range
- SQL raised to a power of error "An invalid floating point operation occurred."
- Check for empty GUID in SQL
- Add html content to Microsoft SQL Server database asp net
- How to select two values in one row?
- List of input values for a Single Input parameter Stored Procedure
- Compare 2 Database all rows - different rows = output
- Compilation of Procedures vs Function in SQL Server
- SQL String: Counting Words inside a String
- Best way to create and connect a Windows Phone 8 app to a database (SQL Server preferably)
- Linq to sql Distinct after join
- What is enough to store dates/times in the DB from multiple time zones for accurate calculations?
- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=
- SQL Server - Stored procedures slow vs "Giant" script
- Why SQL Server add Sort after Merge Join
- OPTIMIZING LEFT JOIN in SQL SERVER
- Compatible DDL (CREATE TABLE) across different SQL databases?
- reading from IDbCommand using an inherited custom IDataReader
- Use WHERE clause to find a specific data type
- Is it possible for two SQL transactions to interleave on read?
- SQL FOR DATA BETWEEN JOIN DATE and LEAVING DATE OF EMPLOYEE
- How do I flip a bit in SQL Server?
- SSIS Package Not Populating Any Results
Source:
stackoverflow.com