Pivot Table SQL Server Nvarchar

Question


SELECT oldName into #tmpProc  from(
SELECT DISTINCT p.OldmbleAI,p.ProductNo, 
    FROM Comidas c
    ) AS tb
    WHERE oldname<>''

SELECT * FROM  #tmpProc
PIVOT( min(#tmpProc.oldname)
for #tmpProc.oldname in ([1],[2],[3],[4]))as pvTable

Result:

[![enter image description here][1]][1]

I want put the productNo into columns [1],[2] etc , i cant, i tried with

Sample data would really help -- *as a text table in the question*. — Gordon Linoff, Nov 25 '19 at 20:52
Please add the definition of `#tmpProc` and insert statements of sample data. — Gert Arnold, Nov 25 '19 at 20:56
That's not sample data, that's a picture of your data after your failed attempt. We need to know what your data looks like *before* your failed attempt and it needs to be `text`, not an image. We can't use an image. — Thom A, Nov 25 '19 at 22:06

score 1 · Answer 1 · answered Nov 25 '19 at 22:07

Try putting the source query in a sub-query.
With only the required colums.

A MAX works for strings also.

SELECT * 
FROM (
    SELECT Subensamble, Proceso#, ProductNo 
    FROM #tmpProc
) AS Src
PIVOT(
    MAX(ProductNo) 
    FOR Proceso# IN ([1],[2],[3],[4])
) AS Pvt;

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

The following are a few suggestions on how I'd write the SQL

Source Control

If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free & private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.

Formatting

I would download the following tool for SSMS and Visual Studio, Poor Man's T-Sql Formatter and on GitHub. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.

Here are the settings I used:

Commas

I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.

Common Table Expressions (CTE)

CTE's in your SQL help with documentation. The expression name can then let other developers know why you used that expression e.g. current_suppliers or active_projects.

Schema Names

Always reference the schema when selecting an object e.g. [dbo].[SalesTable].

Estimated Execution Plan

It's a good idea to check the Estimated Execution Plan. The shortcut in Microsoft SQL Server Management Studio (SSMS) is Ctrl + L. You can even run 2 queries in the same tab to compare the plans.

Also check out the book Clean Code. It will change the way you think about naming conventions.

Revised SQL

WITH
test_data
AS
(
   SELECT tbl.* FROM (VALUES
    ( '1AD-SA02096', '1AD-SA02096E-2', 2)
   , ( '1AD-SA02096', '1AD-SA02096E-1', 3)
   ) tbl ([Subensamble], [ProductNo], [Proceso]) 
)
SELECT
    [Subensamble]
   , [1]
   , [2]
   , [3]
   , [4]
FROM
   test_data AS src 
      PIVOT  
      (  
      MAX([ProductNo])  
      FOR [Proceso] IN ([1], [2], [3], [4])
   ) AS pvt;

Results

Pivot Table SQL Server Nvarchar

2 Answers2

The following are a few suggestions on how I'd write the SQL