Display column values next to each other SQL

how to use values from previous or next rows in a sql server query
sql: selecting rows where column value changed from previous row
how to compare current value with previous value in sql
sql query to select record having same id but different value in two columns
sql select pairs of values
sql cross join
how to get next row value in sql server 2008
sql where pair of values

I have to select which wards have patients with the same last name. I need to display the name of those patients and the IDs that come with them.

My query below works to give me the proper result:-

SELECT pt.NAME, pt.PATIENTID, pt.PATIENTID
FROM PATIENT pt
WHERE pt.NAME IN
    (SELECT pt.NAME
    FROM PATIENT pt
    GROUP BY pt.NAME
    HAVING COUNT(DISTINCT pt.WARDNO) < 2)
AND NAME IN
    (SELECT NAME
    FROM PATIENT
    GROUP BY NAME
    HAVING COUNT(*) > 1)

It gives me the following result in the table:

Name   |   PatientID
Jones  |   p10
Jones  |   p29

However, I want to display my results in the table like this and am not sure how.

Name   |   PatientID   |   PatientID
Jones  |   p10         |   p29

Assuming you expect no more than two patient ID values, then a simple aggregation can work here:

WITH cte AS (
    SELECT NAME
    FROM PATIENT
    GROUP BY NAME
    HAVING COUNT(*) > 1 AND COUNT(DISTINCT WARDNO) < 2
)

SELECT
    NAME,
    MIN(PATIENTID) AS PatientID1,
    MAX(PATIENTID) AS PatientID2
FROM cte
GROUP BY
    NAME;

Query for pair-wise relationships between rows for a SQL Server table, If the linked column value for a source row occurs just once or is not populated, Rows in the source table that do not link to other rows by a value, such as The next screen shot shows a data source for pair-wise linking that� SELECT column_name[, column_name] FROM table_list. Separate each column name from the following column name with a comma. Rearranging Result Columns. The order in which columns appear in a display is completely up to you: Use the SELECT list to order them in any way that makes sense. Following are two examples.

Thank you Tim. That aggregation did work and I did a little tweak to make it a lot cleaner. It works to display just the two PatientIDs which is what I need for now. My new query is as follows

SELECT pt.NAME, MIN(pt.PATIENTID) AS PAT, MAX(pt.PATIENTID) AS PAT
FROM PATIENT pt
GROUP BY pt.NAME
HAVING COUNT(*) > 1 AND COUNT(DISTINCT pt.WARDNO) < 2

However, if I do have multiple PatientIDs I am curious what I would do.

How to Use Values from Previous or Next Rows in a SQL Server Query, The problem is that SQL queries perform operations on a row-by-row basis; accessing data on different rows at the same time requires the query to do some extra work. 007-rownumber-column-Next-Rows-in-a-Query-; The� It is to display the column data like that in the report. no adding the columns. i have one more flag, can i do the same in decode? Id first_flag second_flag third_falg. 1 y n y. 2 n y y. 3 y y n. 4 y y y. 5 n n y i want to display the data like below id 1 ----- flag1 , flag3 (first_flag ='Y' and third_flag ='Y' )

I have to select which wards have patients with the same last name.

Assuming patients are not duplicated, you can get the patient/ward combinations as:

select p.wardno, p.name, count(*)
from patient p
group by p.wardno, p.name
having count(*) >= 2;

If you only want the wards, then you have one of the rare situations where select distinct and group by are appropriate:

select distinct p.wardno
from patient p
group by p.wardno, p.name
having count(*) >= 2;

SQL UNION, UNION allows you to stack one dataset on top of another dataset. the results of one statement display in the same table as the results from the other statement. By: Douglas P. Castilho | Updated: 2019-05-03 | Comments (92) | Related: More > T-SQL Problem. I need a way to roll-up multiple rows into one row and one column. I know I can roll-up multiple rows into one row using Pivot, but I need all of the data concatenated into a single column in a single row.

10 SQL tricks that you didn't think were possible, In order to understand the value of these 10 SQL tricks, it is first Add an index on the search field: The country (this tells the database that One impressive example that frequently shows up on blogs: The Mandelbrot Set, e.g. as displayed on In other words, we take the “closest preceding” “LO” value. The SQL UNION operator. SQL joins allow you to combine two datasets side-by-side, but UNION allows you to stack one dataset on top of the other. Put differently, UNION allows you to write two separate SELECT statements, and to have the results of one statement display in the same table as the results from the other statement.

Multiple options to transposing rows into columns, The objective of this article is to demonstrate different SQL Server T-SQL options Script 2 displays a T-SQL code that can be used to transpose rows into FETCH NEXT FROM policyDocs_csr INTO @PolNumber, @PolType, @Eff, FROM [dbo].[InsuranceClaims] c FOR XML PATH(''), TYPE. ).value('. Unlike other SQL aggregate functions, the SUM() function accepts only the expression that evaluates to numerical values. You can specify either ALL or DISTINCT modifier in the SUM() function. The DISTINCT modifier instructs the SUM() function to calculate the total of distinct values, which means the duplicates are eliminated.

SQL joins and how to use them, Different joins available in SQL are explained -- inner, left, right, and cross joins. The value in the id column of the users table for the user Jane Smith is 5 ; since this Another type of outer join is a RIGHT JOIN , which we'll look at next. Let's make a RIGHT JOIN or RIGHT OUTER JOIN that displays all reviews and their� Next Steps. There are other ways to break the tie when more than one record contains the min or max. Also, make sure to test your code to ensure accuracy. Finally, please check out these other tips and tutorials on T-SQL on MSSQLTips.com. SQL Server Min Max Column Normalization for Data Mining; SQL Server TSQL Aggregate Functions; Our complete

Comments
  • You need some sort of pivoting logic here, but my first question for you is what happens if a given name has three patient IDs? What about four?
  • That is another part that I am curious about. if there were to be four different patient IDs, how would I display the shared Name with all 4 patientIDs next to each other in the table. However, in this instance, I do not need to do that.