How do you select Column Names and grab the data for SQL Server?

Question

I would like to ask for help getting a SELECT statement that would help me in my current predicament. I'm making a new project in vb.net using SQL Server and one of the reports my employer would like me to create is to grab a part name and quantity required using data from an excel file they gave me, however, it works like this:

The data is actually a lot larger than this (like 200+ parts) but this is the general idea. I will have the DWG name going into it, and I would like a SELECT that would have an output like this:

(If users is going by DWG1:)

DWG1     1    Part3  
DWG1     2    Part4
DWG1     1    Part5

Is there a way to grab only the column names that have numbers above 0 in them, get what the precise number is and then that column name? I don't think the people who gave me the Excel file have proper Table structure in mind.

Answer 1

Starting from a spreadsheet with this data:

I used a small program to read from the spreadsheet file and output the data as you showed:

Option Infer On
Option Strict On

Imports System.Data.OleDb

Module Module1

    Class Part
        Property Name As String
        Property Quantity As Decimal

        Public Overrides Function ToString() As String
            Return $"{Name}: {Quantity}"
        End Function

    End Class

    Class Dwg
        Property Name As String
        Property Parts As List(Of Part)

        Public Overrides Function ToString() As String
            Return $"{Name} - {String.Join(", ", Parts)}"
        End Function

    End Class

    Sub Main()
        Dim xlFile = "C:\temp\PartsList.xlsx"
        Dim connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={xlFile};Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

        Dim dt As New DataTable

        Using conn = New OleDbConnection(connStr)
            Dim qry = "SELECT * FROM [Sheet1$]"
            Using da = New OleDbDataAdapter(qry, conn)
                da.Fill(dt)
            End Using
        End Using

        ' To show the column names...
        'For Each col As DataColumn In dt.Columns
        '   Console.WriteLine(col.ColumnName)
        'Next

        Dim dwgs As New List(Of Dwg)

        ' Read the data into a list... '
        For i = 0 To dt.Rows.Count - 1
            Dim dx As New Dwg With {.Name = CStr(dt.Rows(i)(0))}
            Dim parts As New List(Of Part)
            For j = 1 To dt.Columns.Count - 1
                Dim qty = CDec(dt.Rows(i)(j))
                If qty <> 0 Then
                    parts.Add(New Part With {.Name = dt.Columns(j).ColumnName, .Quantity = qty})
                End If
            Next
            dx.Parts = parts
            dwgs.Add(dx)
        Next

        ' Show all the data read... '
        Console.WriteLine("ALL DWGS:-")
        For Each d As Dwg In dwgs
            Console.WriteLine(d.ToString())
        Next
        Console.WriteLine()

        ' Show the data from a selected item... '
        Dim selectedDwg = "DWG1"

        Console.WriteLine($"Drawing {selectedDwg}:-")
        Dim dw = dwgs.First(Function(x) x.Name = selectedDwg)

        If dw IsNot Nothing Then
            For Each p In dw.Parts
                Console.WriteLine($"{dw.Name,-10}{p.Quantity,-8}{p.Name,-12}")
            Next
        End If

        Console.ReadLine()

    End Sub

End Module

Outputs:

ALL DWGS:-
DWG1 - Sprocket: 1, Widget: 2, Part 5: 1
DWG2 - Part 5: 1
DWG3 - Part 1: 1, Sprocket: 2, Widget: 1

Drawing DWG1:-
DWG1      1       Sprocket
DWG1      2       Widget
DWG1      1       Part 5

Once you have the data in the program, it would be easy to insert it into a database such as SQL Server with a proper schema.

[I didn't use Excel to create the spreadsheet - I used LibreOffice Calc and saved it as an xlsx file, and I had to install the Microsoft Access Database Engine 2010 Redistributable for the Provider in the connection string.]

Answer 2

UNPIVOT would be more performant, but if you DON'T want to specify 200 fields, consider this:

Declare @YourTable table (DWG varchar(25),Part1 int,Part2 int,Part3 int,Part4 int,Part5 int)
Insert into @YourTable values
('DWG1',0,0,1,2,1),
('DWG2',0,0,0,0,1)

Select C.*
  From @YourTable A
  Cross Apply (Select XMLData=cast((Select A.* for XML RAW) as xml)) B
  Cross Apply (
                Select DWG   = r.value('@DWG','varchar(25)')              --<< case sensitive
                      ,Item  = attr.value('local-name(.)','varchar(100)')
                      ,Value = attr.value('.','varchar(max)')             --<< can be int if desired
                 From  B.XMLData.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*') as B(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('DWG','OtherFieldsToExclude')
              ) C
  --Where A.DWG='DWG1'

Returns

DWG     Item    Value
DWG1    Part1   0
DWG1    Part2   0
DWG1    Part3   1
DWG1    Part4   2
DWG1    Part5   1
DWG2    Part1   0
DWG2    Part2   0
DWG2    Part3   0
DWG2    Part4   0
DWG2    Part5   1