Show multiple rows and columns using subquery under SELECT clause - sql-server

I want to show the number of clerks and managers in each department.
Here is my code:
SELECT DISTINCT
deptno AS 'DEPARTMENT NUMBER',
(SELECT COUNT(*)
FROM EMP
WHERE JOB = 'CLERK'
GROUP BY DEPTNO) AS 'NUMBER OF CLERKS',
(SELECT COUNT(*)
FROM EMP
WHERE JOB = 'MANAGER'
GROUP BY DEPTNO) AS 'NUMBER OF MANAGER'
FROM
EMP
GROUP BY
deptno
Below is the error returned:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I wanted to show the result like this:
+-------------------+------------------+---------------------+
| DEPARTMENT NUMBER | NUMBER OF CLERKS | NUUMBER OF MANAGERS |
+-------------------+------------------+---------------------+
| 10 | 1 | 1 |
| 20 | 2 | 1 |
| 30 | 1 | 1 |
+-------------------+------------------+---------------------+

This kind of calculation is usually easiest to do using sum + case with something like this:
SELECT
deptno,
sum (case when JOB = 'CLERK' then 1 else 0 end) AS [NUMBER OF CLERKS],
sum (case when JOB = 'MANAGER' then 1 else 0 end) AS [NUMBER OF MANAGERS]
FROM
EMP
GROUP BY
deptno

you can also do it using PIVOT
DECLARE #EMP table (Dep_No INT, Job VARCHAR(20))
INSERT INTO #EMP
VALUES (10,'clerk'),
(10,'clerk'),
(10,'clerk'),
(10,'Manager'),
(20,'clerk'),
(20,'clerk'),
(20,'clerk'),
(20,'clerk'),
(20,'clerk'),
(20,'clerk'),
(20,'Manager'),
(20,'Manager'),
(30,'clerk'),
(20,'Manager'),
(30,'clerk')
SELECT Dep_No , [clerk] Number_Of_Clerks ,[Manager] Number_of_Managers
FROM #EMP
PIVOT
(
COUNT(Job) FOR Job in ([clerk], [Manager])
) W

Related

Need T-SQL update statement to dynamically assign a record number

I have a data set that I got from XML and have it broken out with the following structure:
[Data Table]
[ID] [Name] [Value]
1 ad1_pk 1
2 ad1_addr1 123 Easy Street
3 ad1_pk 2
4 ad1_addr1 99 US31
5 ad1_atfk 6
6 ad1_pk 3
... {and so on}
I have added a column (called recNum) to indicate the distinct record number; however, I have not found a quick way to set the record number for each record. The number of rows that indicate a distinct record can vary, so I want the update statement to be able to handle this. Each "record" has a "column" name that ends with "_pk", so that's how I'm determining the start of each record in the dataset.
I have done this successfully with a while loop, but it's way too slow and tables can have millions of records. Example:
DECLARE #maxRowID INT = (SELECT MAX(ID) FROM myTable)
DECLARE #i INT = 1
DECLARE #currentRecordID INT = 1
WHILE #i<#maxRowID AND #i<100 BEGIN
IF (SELECT RIGHT(name,3) [name] FROM myTable WHERE ID=#i)='_pk' AND #i>1 BEGIN
SET #currentRecordID = (SELECT DISTINCT value FROM myTable WHERE id=#i)
RAISERROR('Record=%i',0,1,#currentRecordID) WITH NOWAIT
UPDATE z2
SET recNum=#currentRecordID
FROM myTable z2
WHERE id=#i
END ELSE BEGIN
UPDATE z2
SET recNum=#currentRecordID
FROM myTable z2
WHERE id=#i
END
SET #i = #i+1
END
Does anybody have a suggestion to do this in a quick manner w/o using a cursor?
My ultimate goal is to insert statements into an SQL table (already created) with the following format:
insert into myNewTable ({column name list}) VALUES ({value list})
...
[updated 2015-06-24 00:26 EDT]
This is how far I have gotten thus far...
https://drive.google.com/file/d/0B82UP-AIFz_ITlNIb1ZwSFdyODg/view?usp=sharing
SELECT TOP 100
z2.ID,z2.Name,z2.Value,CASE WHEN z2.ID=RecIDs.ID THEN z2.Value ELSE NULL END RecNum
FROM MyTable 2
LEFT JOIN (
SELECT DENSE_RANK() OVER (ORDER BY ID) drn,ID FROM MyTable
WHERE RIGHT(name,3)='_pk'
) RecIDs ON RecIDs.ID = z2.ID
ORDER BY ID
... I need to fill in the gaps.
Any suggestions?
[updated 2015-06-25 09:33 EDT]
This is for SQL Server 2008 R2
First, you want to assign a value for RecNum for all PK using ROW_NUMBER. After that, you want to update the remaining rows with the appropriate RecNum
SQL Fiddle
WITH CtePKs AS(
SELECT *,
RN = ROW_NUMBER() OVER(ORDER BY ID)
FROM z2
WHERE RIGHT(Name, 3) = '_pk'
)
UPDATE CtePKs SET RecNum = RN
UPDATE z
SET RecNum = x.RecNum
FROM z2 z
OUTER APPLY(
SELECT TOP 1 Id, RecNum
FROM z2
WHERE
ID < z.ID
AND RecNum IS NOT NULL
ORDER BY ID DESC
)x
WHERE z.RecNum IS NULL
RESULT
| ID | Name | Value | RecNum |
|----|-----------|-----------------|--------|
| 1 | ad1_pk | 1 | 1 |
| 2 | ad1_addr1 | 123 Easy Street | 1 |
| 3 | ad1_pk | 2 | 2 |
| 4 | ad1_addr1 | 99 US31 | 2 |
| 5 | ad1_atfk | 6 | 2 |
| 6 | ad1_pk | 3 | 3 |
If I understand your question correctly, you can use a correlated subquery like this. If you are using SQL Server 2012 or above, you can use SUM() OVER() as well. Check Edit.
Sample Data
CREATE TABLE Table1
([ID] int,rowid int, [Name] varchar(9), [Value] varchar(15));
INSERT INTO Table1
([ID], [Name], [Value])
VALUES
(1, 'ad1_pk', '1'),
(2, 'ad1_addr1', '123 Easy Street'),
(3, 'ad1_pk', '2'),
(4, 'ad1_addr1', '99 US31'),
(5, 'ad1_atfk', '6'),
(6, 'ad1_pk', '3');
Query
UPDATE Table1
SET rowid =
(SELECT COUNT(ID) FROM Table1 T2 WHERE T2.ID <= Table1.ID AND T2.Name Like '%[_]pk');
SELECT * FROM Table1;
SQL Fiddle
OUTPUT
| ID | rowid | Name | Value |
|----|-------|-----------|-----------------|
| 1 | 1 | ad1_pk | 1 |
| 2 | 1 | ad1_addr1 | 123 Easy Street |
| 3 | 2 | ad1_pk | 2 |
| 4 | 2 | ad1_addr1 | 99 US31 |
| 5 | 2 | ad1_atfk | 6 |
| 6 | 3 | ad1_pk | 3 |
EDIT
For SQL Server 2012 or above
Query
;WITH CTE AS
(
SELECT SUM(CASE WHEN Name LIKE '%[_]pk' THEN 1 ELSE 0 END) OVER(ORDER BY ID) recnum,*
FROM Table1
)
UPDATE CTE
SET rowid = recnum;
SELECT * FROM Table1;
SQL Fiddle
You can use a cte for that:
;With cte as (
SELECT [id], Row_number() OVER(Order by [id] As rn
FROM MyTable
)
UPDATE MyTable
SET recNum = rn
FROM MyTable t
INNER JOIN cte ON(t.[id] = cte.[id])
However, you since already have an id column that seems to have the values you are asking for, you can simply do this:
UPDATE MyTable
SET recNum = [id]

Hide rows when column values are duplicate

I have a table named "letters" with two columns looking like this:
case_nr | date
--------+-----------------------
1 | 2015-06-13 12:45:04
1 | NULL
2 | 2015-06-11 12:45:09
3 | 2015-06-12 17:41:49
3 | 2015-06-13 18:42:99
case_nr 1 have printed 2 letters but only one was sent
I want to filter all cases where all letters was sent (have a date)
So in this case result should be:
2
3
You can use DISTINCT with NOT IN:
SELECT DISTINCT case_nr
FROM TableName
WHERE case_nr NOT IN
(SELECT case_nr FROM TableName WHERE [date] IS NULL )
Result:
case_nr
--------
2
3
Sample result in SQL Fiddle.
Group by the case_nr and take only those having no record with date is null
select case_nr
from your_table
group by case_nr
having sum(case when date is null then 1 else 0 end) = 0
SQLFiddle demo
As an alternative:
SELECT
case_nr
FROM (
SELECT
case_nr,
COUNT(CASE WHEN [date] IS NULL THEN 1 END) AS cnt
FROM
yourTable
GROUP BY
case_nr
) t
WHERE
cnt = 0

Right using of PIVOT-statement in SQL Server

I have an audit table, part of data is looked like:
Id ColumnName Value RowId
---------------------------------
1 EditCheckId 100 1
2 FieldData_Id 10 1
3 EditType 1 1
4 Outcome True 1
5 EditCheckId 200 2
6 FieldData_Id 20 2
7 EditType 2 2
8 Outcome False 2
9 EditCheckId 300 3
10 FieldData_Id 30 3
11 EditType 3 3
12 Outcome True 3
and I want to construct such table groupping data by RowId
EditCheck_Id FieldData_Id EditType Outcome
---------------------------------------------------
100 10 1 True
200 20 2 False
300 30 3 True
I've tried the query:
select [EditCHeck_Id], [FieldData_Id], [EditType], [Outcome]
from
(
select [ColumnName], [Value]
from Audit a
) as [SourceTable]
pivot
(
max([Value])
for [ColumnName] in ([EditCHeck_Id], [FieldData_Id], [EditType], [Outcome])
) as [PivotTable];
http://sqlfiddle.com/#!6/7af71/3
using PIVOT statement but there is only one row in answer. Where is my problem?
You'll need some value to GROUP BY to make each of the rows distinct. Typically, you would use a windowing function like row_number() to generate a unique sequenced number over your current columnname. You can alter your query to the following:
select
[EditCheckId],
[FieldData_Id],
[EditType],
[Outcome]
from
(
select [ColumnName], [Value],
rn = row_number() over(partition by ColumnName order by id)
from Audit a
) as [SourceTable]
pivot
(
max([Value])
for [ColumnName] in ([EditCheckId], [FieldData_Id], [EditType], [Outcome])
) as [PivotTable];
See SQL Fiddle with Demo.
You could also use an aggregate function with a CASE expression to get the final result:
select
max(case when ColumnName = 'EditCheckId' then value end) [EditCheckId],
max(case when ColumnName = 'FieldData_Id' then value end) [FieldData_Id],
max(case when ColumnName = 'EditType' then value end) [EditType],
max(case when ColumnName = 'Outcome' then value end) [Outcome]
from
(
select [ColumnName], [Value],
rn = row_number() over(partition by ColumnName order by id)
from Audit a
) d
group by rn;
See SQL Fiddle with Demo Both give the result:
| EDITCHECKID | FIELDDATA_ID | EDITTYPE | OUTCOME |
|-------------|--------------|----------|---------|
| 100 | 10 | 1 | True |
| 200 | 20 | 2 | False |
| 300 | 30 | 3 | True |
You need something to distinguish the output rows, like for example your RowId
pivot does the grouping for you like so:
select [RowID], [EditCheckId], [FieldData_Id], [EditType], [Outcome]
from
(
select [ColumnName], [Value], [RowId]
from Audit a
) as [SourceTable]
pivot
(
max([Value])
for [ColumnName] in ([EditCheckId], [FieldData_Id], [EditType], [Outcome])
) as [PivotTable];
edited Fiddle demo

Sum with SQL server RollUP - but only last summary?

I have this query:
DECLARE #t TABLE(NAME NVARCHAR(MAX),datee date,val money)
insert INTO #t SELECT 'a','2012-01-02',100
insert INTO #t SELECT 'a','2012-01-02',100
insert INTO #t SELECT 'a','2012-01-03',100
insert INTO #t SELECT 'a','2012-01-05',100
insert INTO #t SELECT 'b','2012-01-06',200
insert INTO #t SELECT 'b','2012-01-07',200
insert INTO #t SELECT 'd','2012-01-07',400
insert INTO #t SELECT 'e','2012-01-09',500
insert INTO #t SELECT 'f','2012-01-12',600
SELECT Name,datee,SUM (val)
from #t GROUP BY NAME ,datee
currently the result is:
BUT I need to add sum at the end.
So I tried with rollup:
SELECT Name,datee,SUM (val)
from #t GROUP BY NAME ,datee with ROLLUP
BUT I only need the last sum total line. I don't need the in-report sum's
So how can get the desire result?
(I cant change the group by clause cause others need it also , I just want to add sum at the end with/without rollup).
sql online is here
It's possible with GROUPING SETS, try this:
SELECT Name,datee,SUM (val)
FROM #t
GROUP BY
GROUPING SETS((NAME ,datee), ())
SQL Fiddle
It is also possible with ROLLUP():
SELECT
Name,
datee,
SUM (val)
FROM #t
GROUP BY
ROLLUP((NAME, datee))
;
WITH ROLLUP, as well as WITH CUBE, are non-standard and deprecated. (See Non-ISO Compliant Syntax in the GROUP BY manual.)
It should be noted that ROLLUP() isn't supported in compatibility level under 90 in SQL Server 2005 or under 100 in SQL Server 2008+, while GROUPING SETS() is.
If you just want the final total, can't you just use a UNION ALL:
SELECT Name,datee,SUM (val)
from #t
GROUP BY NAME ,datee
union all
SELECT null,null,SUM (val)
from #t
See SQL Fiddle with Demo
Or you can use a WHERE clause to filter the rows with the null values:
select name,
datee,
total
from
(
SELECT Name,datee,SUM (val) total
from #t
GROUP BY NAME, datee with rollup
) src
where datee is not null
or
(
name is null
and datee is null
)
See SQL Fiddle with Demo
The result is:
| NAME | DATEE | COLUMN_2 |
----------------------------------
| a | 2012-01-02 | 200 |
| a | 2012-01-03 | 100 |
| a | 2012-01-05 | 100 |
| b | 2012-01-06 | 200 |
| b | 2012-01-07 | 200 |
| d | 2012-01-07 | 400 |
| e | 2012-01-09 | 500 |
| f | 2012-01-12 | 600 |
| (null) | (null) | 2300 |
You can use this query :
SELECT *
FROM ( SELECT Name ,
datee ,
SUM(val) summ
FROM #t
GROUP BY NAME ,
datee
WITH ROLLUP
) A
WHERE ( datee IS NOT NULL
OR ( datee IS NULL
AND name IS NULL
)
)

Is it possible to “merge” two rows in the result set of a SQL Server query?

I'm looking at a query that outputs something like the following:
Group | Date 1 | Value 1 | Date 2 | Value 2
------+------------+---------+-------------+--------
A | 2011-06-15 | 105 | NULL | NULL
A | NULL | NULL | 2011-06-16 | 107
B | 2011-06-18 | 567 | NULL | NULL
B | NULL | NULL | 2011-06-20 | 525
What I want is to "flatten" these results by group - sort of like a COALESCE by row:
Group | Date 1 | Value 1 | Date 2 | Value 2
------+------------+---------+-------------+--------
A | 2011-06-15 | 105 | 2011-06-16 | 107
B | 2011-06-18 | 567 | 2011-06-20 | 525
Note that there will only ever be 2 rows per group; this isn't dealing with an arbitrary number of data points, it's essentially a "before and after" query.
Is it possible to do this in a single pass? (There is a large amount of data in the results). That means:
No temp tables, table variables, or similar intermediate steps (CTEs are fine);
No joining to itself, i.e. the obvious solution of getting the MIN and MAX in one query and then joining to those dates to get the values. As mentioned above, this is the result set of an expensive query, and I am trying very hard not to double the load.
I know that I could technically do this with a cursor, but I'd really really prefer not to unless somebody can prove to me that it is faster than any set-based option.
P.S. Please note that for group "B", Value 2 is lower than Value 1. I've done this explicitly to demonstrate why a single GROUP BY which takes the MIN and MAX of both dates and values isn't going to produce the expected results. The values have to be correlated to the dates.
Assuming that you always get pairs in the Groups, you can do a GROUP BY [Group] and for each of the 4 columns select the MAX() (which will basically take the non-null value).
Edit: if you have a dataset with the columns "Group", "Date" and "Value" and want to merge those so that you have the "Group", "Date 1", "Value 1", "Date 2" and "Value 2" columns, use this (the first CTE represents the test data):
WITH Results AS (
SELECT 'A' [Group], CONVERT(datetime, '2011-06-15') [Date], 105 [Value]
UNION ALL
SELECT 'A' [Group], CONVERT(datetime, '2011-06-16') [Date], 107 [Value]
UNION ALL
SELECT 'B' [Group], CONVERT(datetime, '2011-06-18') [Date], 567 [Value]
UNION ALL
SELECT 'B' [Group], CONVERT(datetime, '2011-06-20') [Date], 525 [Value]
),
ResultsIndexed AS (
SELECT ROW_NUMBER() OVER (PARTITION BY r.[Group] ORDER BY r.[Date]) ix, r.* FROM Results r
)
SELECT r1.[Group], r1.[Date] [Date 1], r1.[Value] [Value 1], r2.[Date] [Date 1], r2.[Value] [Value 1]
FROM ResultsIndexed r1
JOIN ResultsIndexed r2 ON (r1.[Group] = r2.[Group]) AND (r2.[ix] = 2)
WHERE r1.[ix] = 1
Not sure if this is what you want. If you just have two row per group, you may try this.
;WITH cte as
(
-- You sql output here
SELECT *
FROM table
)
SELECT a1.group,a1.date1,a1.value1,a2.date2,a2.value2
FROM cte a1
INNER JOIN cte a2
ON a2.date2 IS NOT NULL
AND a1.group = a2.group
WHERE a1.date1 IS NOT NULL

Resources