15147

How to convert many rows into Columns in SQL Server?

Question:

How would you convert a field that is stored as multiple rows into columns? I listed the code below as well. Below is an example of what is needed but it can really go up to 20 columns. Thanks!

COL1 COL2 COL3 ---------------- TEST 30 NY TEST 30 CA TEST2 10 TN TEST2 10 TX

I would like the output to be :

COL1 COL2 COL3 COL4 ------------------------ TEST 30 NY CA TEST2 10 TN TX

<br />

select * from ( select ID, Name, STORE, Group, Type, Date, State, row_number() over(partition by ID, state order by Date desc) as rn from #test ) t where t.rn = 1

Answer1:

There are multiple options to convert data from rows into columns. In SQL, you can use PIVOT to transform data from rows into columns.

CREATE table #tablename (Id int, Value varchar(10), ColumnName varchar(15); INSERT INTO #tablename (ID, Value, ColumnName) VALUES (1, ‘Lucy’, 'FirstName'), (2, ‘James’, ‘LastName’), (3, ‘ABCDXX’, ‘Adress’), (4, ’New York’, ‘City’), (5, '8572685', ‘PhoneNo’); select FirstName, LastName, Address, City, PhoneNo from ( select Value, ColumnName from #tablename ) d pivot ( max(Value) for ColumnName in (FirstName, LastName, Address, City, PhoneNo) ) piv;

Refer the below link for other options of transforming data from rows to columns:

<a href="https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/" rel="nofollow">https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/</a>

Answer2:

declare @Table AS TABLE ( Col1 VARCHAR(100) , Col2 INT , Col3 VARCHAR(100) ) INSERT @Table ( Col1, Col2, Col3 ) VALUES ( 'TEST', 30 ,'NY' ), ( 'TEST', 30 ,'CA' ), ( 'TEST2', 10 ,'TN' ), ( 'TEST2', 10 ,'TX' ) SELECT xQ.Col1, xQ.Col2, MAX(CASE WHEN xQ.RowNumber = 1 THEN xQ.Col3 ELSE NULL END) AS Col3, MAX(CASE WHEN xQ.RowNumber = 2 THEN xQ.Col3 ELSE NULL END) AS Col4 FROM ( SELECT * , RANK() OVER(PARTITION BY T.Col1,T.Col2 ORDER BY T.Col1,T.Col2,T.Col3) AS RowNumber FROM @Table AS T )AS xQ GROUP BY xQ.Col1, xQ.Col2

Recommend

  • Time Consumption of FlushViewOfFile (Windows) and msync(Linux)
  • Implementing renaming and deletion in java watchservice
  • Entering If Statement Despite Not Meeting Condition
  • Exporting PDF's from SQL Server DB and writing a Map to a Text File
  • three.js transparent png texture strange border webgl
  • Getting SPI temperature data from outside of class
  • Permission for an image from Gallery is lost after re-launch
  • Fade In circles in Google Maps
  • How do I get partial cell styling in excel using EPpplus?
  • Printing a flowdocument with dynamic data in WPF
  • MySQL database structure for a webshop
  • Retrieve 3rd MAX salary in Hive
  • Apache same orgin request blocking
  • python pandas isin method?
  • Newtonsoft.json serializing and deserializing base/inheirited where classes are from shared projects
  • Raw sockets in monotouch
  • Google Apps Script fails to generate image from EmbeddedChartBuilder
  • .htaccess redirect domain.com to www.domain.com
  • Issues with converting data into a matrix after running lapply()
  • How to put an object in the air?
  • internal javascript not works in angular2
  • Facebook friend list in Facebook Android SDK 3.14
  • Synchronous Calls with jqGrid?
  • git clone, upload-pack out of memory
  • Reload Page with Javascript after Database changes
  • Set WebClient.Builder.exchangeStrategies() without losing Spring Jackson configuration
  • Wireshark Display Filter for Unique Source/Destination IP and Protocol
  • how to specify different css for ie
  • Ember.js + JQuery-UI Tooltip - Tooltip does not reflect the model / controller changes
  • C++ STL stack pop operation giving segmentation fault
  • Calculate time from document
  • Support of :after in IE7
  • 'url' requires a non-empty first argument. The syntax changed in Django 1.5, see the docs
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • Amazon Elastick BeanStalk error: Failed to create the AWS Elastic Beanstalk application version
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • php “page caching” solution suggestions for CMS Applications
  • Functions by reference or by variable, which to use when?
  • What is the best way to cache and reuse immutable singleton objects in Java?
  • Bitrate JWplayer