Hot answers tagged

26

What is the logic behind requiring the same length when using UNPIVOT? This question may only be truly answerable by the people who worked on the implementation of UNPIVOT. You might be able to obtain this by contacting them for support. The following is my understanding of the reasoning, which may not be 100% accurate: T-SQL contains any number of ...


22

A common technique for aggregate string concatenation before SQL Server 2017 is using XML data type methods along with STUFF to remove the extra delimiter. In SQL Server 2017 and later, STRING_AGG makes this task much cleaner and easier. The STRING_AGG equivalent: select @colsUnpivot = STRING_AGG(quotename (C.name),',') from sys.columns c ...


14

If you are going to have an unknown number of columns that you will need to unpivot, then you will have to look at implementing dynamic SQL. You can use sys.columns to get the names of all of the columns in your cal table. If you use the following query you will get the list of all of the columns in your table: select C.name from sys.columns c where c....


12

One approach might be to use a #temp table for the values and also introduce a dummy equijoin column to allow for a hash join. For example: -- Create a #temp table with a dummy column to match the hash join -- and the actual column you want CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL) INSERT INTO #values (dummy, Col0) VALUES (0, 'A'), ...


10

The answer is in the error message: You may need to set the compatibility level of the current database to a higher value to enable this feature. Where 'current database' means the context database - i.e. the database the query is executed in, which is not necessarily the same as the database referenced in the query. For example, the following produces ...


9

What you want to do is a cross apply instead of an unpivot. That allows multiple columns to be unpivoted whereas UNPIVOT only allows a single column to be unpivoted. Something like this: CREATE TABLE dbo.Struc ( ID int NOT NULL , Person nvarchar(30) NOT NULL , Age int NOT NULL , Gender char(1) NOT NULL , Month1 int NOT NULL , ...


8

It's easy with a numbers table. Since also qty cannot be more than 10, we only need a very small numbers table: CREATE TABLE numbers ( i int NOT NULL PRIMARY KEY ) ; INSERT INTO numbers (i) VALUES (1), (3), (5), (7), (9) ; We need only the odd numbers because the numbers of rows wanted in the result is half of qty (or about half). The query: SELECT ...


7

Unpivot Table Being the product of a transformation, your desired form here is called an "Unpivot table". The form you're starting from here is either normalized data, or the result of a transformation itself called a "pivot." You can pivot and unpivot without losing data. Pivoting and operations like this emerge from OLAP functionality, where they're ...


7

You can do the unpivot in a cross apply instead to get the result you want. declare @T table ( id int, title varchar(50), genre1 varchar(50), genre2 varchar(50), genre3 varchar(50), genre4 varchar(50), genre5 varchar(50) ); insert into @T values (1, 'Movie1', 'Thriller', 'Crime', NULL, NULL, NULL), (2, 'Movie2', 'Fantasy', NULL, NULL, NULL, ...


6

This sounds more like an UNPIVOT to me. SELECT SalesTable.Pk, CrossApplied.WeekYear, CrossApplied.Value FROM SalesTable CROSS APPLY (VALUES ('01'+CAST([Year] AS CHAR(40)), Week1), ('02'+CAST([Year] AS CHAR(40)), Week2), ('03'+CAST([Year] AS CHAR(40)), Week3), .......) CrossApplied (...


5

As Dan said, but also... Get in the habit of using the text() node in xml values, it won't make much difference to a small result set, but I see no reason to ever NOT use it, and you will really see a benefit in larger data sets. The red section is painful and can be avoided by the use of (./text())[1]


4

This query uses both UNPIVOT and then PIVOT: SELECT piv.[Data], piv.x, piv.y FROM ( SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10)) FROM @data ) d UNPIVOT ( [value] FOR [Data] IN (ColA, ColB, ColC, ColD) ) as unpiv PIVOT ( MAX([value]) ...


4

You can convert the row to a JSON value and then use a sub-select: select t.id, x.roles from the_table t cross join lateral ( select array_agg(col) as roles from jsonb_each_text(to_jsonb(t)) x(col,val) where x.val::boolean and x.col like 'role%' ) x


3

Sample table and data CREATE TABLE dbo.Table1 ( RowID integer IDENTITY PRIMARY KEY, Col2 integer NULL, Col3 integer NULL, Col4 integer NULL, Col5 integer NULL ); INSERT dbo.Table1 (Col2, Col3, Col4, Col5) VALUES (1, 1, 1, 1), (1, 3, 2, 6), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1), (1, 1, 1, 1); Trigger ...


3

Try this: WITH CTE(Assessor,Date,Field) AS ( select Assessor,Date,Field from( select Assessor,Date,ISNULL(Length,'Length') as Length,ISNULL(Colour,'Colour') as Colour,ISNULL(Weight,'Weight') as Weigth from TestTable) as OP unpivot( Field for Fields IN (Length,Colour,Weigth)) as UP) select Assessor,Date,Field from CTE where Field in ('Colour','Length','...


3

UNPIVOT is not the best way to unpivot a table, CROSS APPLY with VALUES is a far better method. Sorry, but I don't think you can get away from Dynamic SQL for this one. This code will dynamically unpivot an entire table. DECLARE @tablename VARCHAR(255) = 'Employee' DECLARE @schemaname VARCHAR(255) = 'HumanResources' DECLARE @qrystr NVARCHAR(4000) = '' ;...


3

Given this test data: DECLARE @T AS TABLE ( ZIP sql_variant NOT NULL, STATE sql_variant NOT NULL, F sql_variant NULL, C sql_variant NULL, F1 sql_variant NULL, C1 sql_variant NULL, F2 sql_variant NULL, C2 sql_variant NULL, F3 sql_variant NULL, C3 sql_variant NULL ); INSERT @T (ZIP, STATE, F, C, F1, C1, F2, C2) ...


3

You can use two SELECTs, one for each Measure. create table tbl2 (ID int identity, ServiceName varchar(100), ServiceCode varchar(100), Age varchar(100), Gender varchar(100), Measure varchar(100), [Value] int, Location varchar(100)); insert into tbl2 (ServiceName, ServiceCode, Age, Gender, Measure, Value, Location) select ...


3

I suggest a VALUES expression in the lateral subquery. SELECT t.id, a.roles FROM tbl t CROSS JOIN LATERAL ( SELECT ARRAY( SELECT col FROM ( VALUES ('role1', role1) -- eligible columns , ('role2', role2) , ('role3', role3) ) x(col, val) WHERE val ) ) a(roles); This pivots (...


3

This answer isn't dynamic, but shows you how to achieve the goal in pure TSQL, making it dynamic should be straightforward. Essentially, you just need to add another column to spread the data out so that the MAX(fieldname) isn't the max for the event. I've chosen to use a ROW_NUMBER partitioned by the EventID so that each entry for each event is also ...


2

As Mark mentioned, UNPIVOT would be a good choice here. I often find it's easier to throw the original query into a temp table then unpivot that. You could use that approach, CTEs or views depending on performance. All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this: IF OBJECT_ID('tempdb....


2

You are missing a new column generated by unpivoting attributes SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue] FROM ( SELECT itemNumber, type, code, description FROM myTable ) AS tb UNPIVOT ( Value FOR attributes IN ( type, code, description) ) AS myValues


2

Why do you need to convert the average into a varchar? Also, why do you have two extra SELECT * FROM (...) AS T0 wrapping the main select statement? This runs without error: USE tempdb; CREATE TABLE [dbo].[tblQuestaoOpcao]( [CodQuestaoOpcao] [int] IDENTITY(1,1) NOT NULL, [CodNRQuestao] [int] NULL, [Opcao] [int] NULL, [DescOpcao] [varchar](...


2

The CROSS APPLY way of doing this would be: SELECT et.Payroll , v.Item , Tax = SUM(v.TaxValue) , Gross = SUM(v.GrossValue) FROM #EmployeeTaxes et CROSS APPLY ( VALUES ('FIT', fit, fit_Gross, 1) , ('Employee_FICA', Employee_FICA, Employee_FICA_Gross, 2) , ('Employer_FICA', Employer_FICA, Employer_FICA_Gross, 3) , ('...


2

This can be done in a step-by-step fashion. First: we have the table sales differentiating between the PurchaseDateTime and the PurchaseDate: CREATE TABLE sales ( UserName varchar(100), PurchaseDateTime datetime, Currency character(3), Price numeric(10,2), Quantity numeric(10,2), PurchaseDate ...


2

Unpivot can be done using an array and unnest: select s.state, s.city, 'item'||t.i as items, t.item from state1 s cross join lateral unnest(array[item1, item2, item3]) with ordinality as t(item,i); Online example: http://rextester.com/VPDDN96018


2

I started with the information in Aaron Bertrand's awesome post Use SQL Server's UNPIVOT operator to dynamically normalize output. You should read the information in that post as Aaron does a great job of explaining the process. I had to make some changes to Aaron's process due the fact that you had more columns that what Aaron had in his example(s). --...


2

Perhaps something like this will suit you: SELECT Unpivoted.* INTO #Unpivoted FROM sys.dm_os_host_info AS HI CROSS APPLY ( VALUES (N'host_platform', CONCAT(HI.host_platform, NULL)), (N'host_distribution', CONCAT(HI.host_distribution, NULL)), (N'host_release', CONCAT(HI.host_release, NULL)), (N'host_service_pack_level'...


2

Unfortunately, MySQL doesn't have an UNPIVOT operator which would be very handy in this case - you'll just have to roll up your sleeves and do it manually I'm afraid. See the fiddle here - I've only done 3 CFWs - you can just copy and paste for the remainder. CREATE TABLE example ( opp_id INTEGER NOT NULL PRIMARY KEY, region VARCHAR (10) NOT NULL, ...


2

What you are missing is a row that can be grouped by. Row_NUMBER is a good solution CREATE TABLE OCCUPATIONS (Name varchar(255), Occupation varchar(255)) ; INSERT INTO OCCUPATIONS (Name, Occupation) VALUES ('Samantha', 'Doctor'), ('Julia', 'Actor'), ('Maria', 'Actor'), ('Meera', 'Singer'), ('Ashely', 'Professor'), ('Ketty', '...


Only top voted, non community-wiki answers of a minimum length are eligible