transpose column into rows from a specific word

Question

I have data in an excel sheet like this:

Number               123456
name                 John
province             Quebec 
Number               458965
name                 sammy
province             ontario

I want to transpose the data into rows.

I want to do this in SQL server by importing the excel file.

Now I am looking for a SQL query which can give me output as shown below. One approach can be that when the query detects Number field name then it should transpose the data into rows till the next Number field appears. The output is shown below.

Number    name   province  client type

use PIVOT or check here https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Muhammad Waheed, Apr 25 '19 at 05:31
@Skin in excel I know this can be done via index but I want to do this in SQL server. — zain ul abidin, Apr 25 '19 at 05:58
@zainulabidin maybe remove the tags referencing Excel then. It doesn’t seem to matter what the source of the data is. If it’s in DB table already you still have the same problem. — Skin, Apr 25 '19 at 06:00

Skin · Accepted Answer · 2019-04-25T07:07:21.543

I'm sure there's a more efficient way to do it but once you have your data in SQL in a temp table (I just hacked a method together to do that, which you could do from your Excel workbook by generating insert statements if you have no other easier way) ...

DECLARE @tblData TABLE
(
    iRowNo      int identity(1,1),
    sField      nvarchar(100),
    sValue      nvarchar(1000)
)

INSERT INTO @tblData VALUES ('Number', '123456')
INSERT INTO @tblData VALUES ('name', 'John')
INSERT INTO @tblData VALUES ('province', 'Quebec')
INSERT INTO @tblData VALUES ('client', 'Client 1')
INSERT INTO @tblData VALUES ('type', 'Type 1')
INSERT INTO @tblData VALUES ('Number', '458965')
INSERT INTO @tblData VALUES ('name', 'sammy')
INSERT INTO @tblData VALUES ('province', 'ontario')
INSERT INTO @tblData VALUES ('client', 'Client 2')
INSERT INTO @tblData VALUES ('type', 'Type 2')

... you can try the below query ...

SELECT iRowNo, sValue AS Number,
    Name = (SELECT TOP (1) B.sValue FROM @tblData AS B WHERE B.sField = 'name' AND B.iRowNo > A.iRowNo ORDER BY iRowNo),
    Province = (SELECT TOP (1) B.sValue FROM @tblData AS B WHERE B.sField = 'province' AND B.iRowNo > A.iRowNo ORDER BY iRowNo),
    Client = (SELECT TOP (1) B.sValue FROM @tblData AS B WHERE B.sField = 'client' AND B.iRowNo > A.iRowNo ORDER BY iRowNo),
    Type = (SELECT TOP (1) B.sValue FROM @tblData AS B WHERE B.sField = 'type' AND B.iRowNo > A.iRowNo ORDER BY iRowNo)
FROM @tblData AS A
WHERE sField = 'Number'

... which gave me the below result ...

... not sure if that helps but based on what you were asking, I think that is the result you're looking for.

I have to admit, I was heavy on the SQL side of things about 8 years ago so there may be a better way to get your answer in this new day and age, or maybe always. :-)

Anyway, worth a shot ...!

transpose column into rows from a specific word

1 Answers1